?
Solved

Avoid returning no results if match not found

Posted on 2004-03-31
6
Medium Priority
?
247 Views
Last Modified: 2008-01-09
Hi

This snippet of code was kindly written by an EE member.

select case when charindex(@WordSix, PageText) > 0 then left(replace(right(PageText, datalength(PageText)-charindex(@WordSix, PageText) +1), @WordSix, '<strong>' + @WordSix + '</strong>'), 40) else '' end

I would like if possible to amend 2 areas.

1) If a search is for example: b&b near blackpool, the above would return nearby with the near in bold. Is there anyway to change it so that it only picks up whole words.

2) Is it possible to change it so that even if there is no match the where clause still return the results. The problem at the moment is, that if @WordSix is empty i.e the visitor only entered 5 words the search will fail as @WordSix I assume returns a null.

Any help would be appreciated.
George
0
Comment
Question by:Tourist_Search
  • 3
  • 3
6 Comments
 
LVL 6

Expert Comment

by:billy21
ID: 10726017
To change this so a result is returned regardless do this:

Select IsNull(
select case when charindex(@WordSix, PageText) > 0 then left(replace(right(PageText, datalength(PageText)-charindex(@WordSix, PageText) +1), @WordSix, '<strong>' + @WordSix + '</strong>'), 40) else '' end
,'')

0
 
LVL 6

Accepted Solution

by:
billy21 earned 2000 total points
ID: 10726050
Woops.  This is better:

Select IsNull(
(select case when charindex(@WordSix, PageText) > 0 then left(replace(right(PageText, datalength(PageText)-charindex(@WordSix, PageText) +1), @WordSix, '<strong>' + @WordSix + '</strong>'), 40) else '' end)
,'')

Forgot the brackets.

Cheers,

Billy
0
 

Author Comment

by:Tourist_Search
ID: 10733199
Hi Billy

Cannot run code as receive error.
Trying to run code as such.

Select 'substring(HotelName, 0, 60) + '...' +  IsNull((case when charindex(@WordOne, PageText) > 0 then left(replace(right(PageText, datalength(PageText)-charindex(@WordOne, PageText) +1), @WordOne, '<strong>' + @WordOne + '</strong>'), 40) else '' end),'') + '' +
CASE WHEN WordOneCount < 10 THEN 20 WHEN WordOneCount BETWEEN 11 AND 15 THEN 10
when Wordonecount > 15 then 30
END +
--More code here

Error message:

Server: Msg 207, Level 16, State 3, Procedure testsproc, Line 12
Invalid column name 'PageText'.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Expert Comment

by:billy21
ID: 10733295
What is PageText?
0
 

Author Comment

by:Tourist_Search
ID: 10733528
Hi Billy

PageText is a column in the table

I've supplied full code below.

WITH RECOMPILE
AS
Select substring(HotelName, 0, 60) + '...' +
+ case when charindex(@WordOne, PageText) > 0 then left(replace(right(PageText, datalength(PageText)-charindex(@WordOne, PageText) +1), @WordOne, '<strong>' + @WordOne + '</strong>'), 40) else '' end + '....' + ' '
+ case when charindex(@WordTwo, PageText) > 0 then left(replace(right(PageText, datalength(PageText)-charindex(@WordTwo, PageText) +1), @WordTwo, '<strong>' + @WordTwo + '</strong>'), 40) else '' end + '....' + ' '
+ case when charindex(@WordThree, PageText) > 0 then left(replace(right(PageText, datalength(PageText)-charindex(@WordThree, PageText) +1), @WordThree, '<strong>' + @WordThree + '</strong>'), 40) else '' end + '....' + ' '
+ case when charindex(@WordFour, PageText) > 0 then left(replace(right(PageText, datalength(PageText)-charindex(@WordFour, PageText) +1), @WordFour, '<strong>' + @WordFour + '</strong>'), 40) else '' end + '....' + ' '
+ case when charindex(@WordFive, PageText) > 0 then left(replace(right(PageText, datalength(PageText)-charindex(@WordFive, PageText) +1), @WordFive, '<strong>' + @WordFive + '</strong>'), 40) else '' end + '....' + ' '
+ case when charindex(@WordSix, PageText) > 0 then left(replace(right(PageText, datalength(PageText)-charindex(@WordSix, PageText) +1), @WordSix, '<strong>' + @WordSix + '</strong>'), 40) else '' end +
'' + '<br>'  + SUBSTRING(HotelDescription, 0, 150) + ''
+ '<br><br>' + '' +  tblTouristCategory.Category + '>' + tblTouristCountry.Country + '>' + tblTouristCity.City + '<br><br>' +  URLLink  '<br><br>' as SearchResults,

CASE WHEN WordOneCount < 10 THEN 20
WHEN WordOneCount BETWEEN 11 AND 15 THEN 10
when Wordonecount > 16 then 30
END +
CASE WHEN WordTwoCount < 10 THEN 20
WHEN WordTwoCount BETWEEN 11 AND 15 THEN 10
when WordTwoCount > 16 then 30
End
+
CASE WHEN WordThreeCount < 10 THEN 20
WHEN WordThreeCount BETWEEN 11 AND 15 THEN 10
when WordThreeCount > 16 then 30
End
+
CASE WHEN WordFourCount < 10 THEN 20 WHEN WordFourCount BETWEEN 11 AND 15 THEN 10
when WordFourCount > 16 then 30 End
+
CASE WHEN WordFiveCount < 10 THEN 20
WHEN WordFiveCount BETWEEN 11 AND 15 THEN 10
when WordFiveCount > 16 then 30
End
+
CASE WHEN WordSixCount < 10 THEN 20
WHEN WordSixCount BETWEEN 11 AND 15 THEN 10
when WordSixCount > 16 then 30
End AS ReturnResults
FROM (
SELECT top 1000 substring(HotelName, 0, 60) + '...' +
SUBSTRING(HotelDescription, 0, 150) + ''
+ '<br><br>' + '' +  tblTouristCategory.Category + '>' + tblTouristCountry.Country + '>' + tblTouristCity.City + '<br><br>' +  URLLink  '<br><br>' as SearchResults,
(LEN(pagetext) - LEN(REPLACE(pagetext, @wordOne, ''))) / LEN(@wordOne) AS WordOneCount,
(LEN(pagetext) - LEN(REPLACE(pagetext, @wordTwo, ''))) / LEN(@wordTwo) AS WordTwoCount,
(LEN(pagetext) - LEN(REPLACE(pagetext, @wordThree, ''))) / LEN(@wordThree) AS WordThreeCount,
(LEN(pagetext) - LEN(REPLACE(pagetext, @wordFour, ''))) / LEN(@wordFour) AS WordFourCount,
(LEN(pagetext) - LEN(REPLACE(pagetext, @wordFive, ''))) / LEN(@wordFive) AS WordFiveCount,
(LEN(pagetext) - LEN(REPLACE(pagetext, @wordSix, ''))) / LEN(@wordSix) AS WordSixCount

FROM tblTouristCountry
--rest of code here
) AS derived
ORDER BY ReturnResults
0
 

Author Comment

by:Tourist_Search
ID: 10733551
Forgot to mention

+ case when charindex(@WordOne, PageText) > 0 then left(replace(right(PageText, datalength(PageText)-charindex(@WordOne, PageText) +1), @WordOne, '<strong>' + @WordOne + '</strong>'), 40) else '' end + '....' + ' '
 Section above highlights word found

CASE WHEN WordThreeCount < 10 THEN 20
WHEN WordThreeCount BETWEEN 11 AND 15 THEN 10
when WordThreeCount > 16 then 30
End

section above counts number of times word found and awards points

George
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question