Solved

Avoid returning no results if match not found

Posted on 2004-03-31
6
221 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

761 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