Solved

Avoid returning no results if match not found

Posted on 2004-03-31
6
219 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 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

770 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