• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

Avoid returning no results if match not found

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
Tourist_Search
Asked:
Tourist_Search
  • 3
  • 3
1 Solution
 
billy21Commented:
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
 
billy21Commented:
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
 
Tourist_SearchAuthor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
billy21Commented:
What is PageText?
0
 
Tourist_SearchAuthor Commented:
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
 
Tourist_SearchAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now