Solved

Avoid returning no results if match not found

Posted on 2004-03-31
6
216 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 6

Expert Comment

by:billy21
Comment Utility
What is PageText?
0
 

Author Comment

by:Tourist_Search
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now