Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Keyword search using a tally or sequence table

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2001-10-07 : 20:01:33
Ryan writes "I am looking for a way of creating a stored procedure that will carry out a keyword search without building up the SQL dynamically in the stored procedure. What I'm after is a method of doing this that doesn't require me to build up a huge string."

Article Link.

Starting Member

2 Posts

Posted - 2002-01-24 : 20:23:03
Is there a way to make the search more accurate?
Let's say a user want to search for 'big'. The procedure would return things like 'bigamy' and 'bigger'. Is there some kind of a word boundary switch that can be used (like \b with regular expressions), to prevent this?
Thanks :)
Go to Top of Page

Jedi Yak

2489 Posts

Posted - 2002-01-25 : 09:44:36
To make this accurate, remove the wildcards in this statement

INNER JOIN Articles ON Articles.ArticleBody like '%' +
Substring(' ' + @keywords + ' ',seq,
CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq)
+ '%'

This is the updated statement:
INNER JOIN Articles ON Articles.ArticleBody like Substring(' ' + @keywords + ' ',seq, CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq)

That should suit your requirements for "accuracy"

Go to Top of Page

Aged Yak Warrior

504 Posts

Posted - 2002-06-03 : 08:59:52
Is there anyway to turn this into a relavance search? like percantage of matches type thing?

Go to Top of Page

Starting Member

10 Posts

Posted - 2002-09-06 : 19:23:07
When I use this method, It does not record a hit for each occurance of the word even though the article claims that it will count each match as a hit. Also, can additional columns be added to the search? And if so, how would it be done?

Go to Top of Page

Starting Member

1 Post

Posted - 2003-03-19 : 08:28:20
Im not sure about using temporary tables.

What happens when a 2nd search is executed whilst the first one is running ?

I have developed 2 search engines, one used ado recordset clientside (eg the Webserver) to hold the search results, then using paging to get the correct set of data from the search results, works great with sessions.

My newest, was a lightweight solution, design to run off an Access Database, I wrote an extensive ASP VBscript to do calcute a complex query that returns only the data needed.

But in both cases, multi user enviroment meant I could rely on temporary tables.

I personally would save myself the hassle and use the built in split function of VBScript:

strStopList = Split("! £ $ % ^ & * ( ) _ + { } : @ ~ < > ? - = [ ] ; # , . / | \ ' q w e r t y u i o p a s d f g h j k l z x c v b n m all an and are as at be but by can for from had have he her him his if in is it may not of on or she that the this those to was we which who will with you your")

' Get the text and trim it
strSearchText = trim(Replace(Request.Form("txtSearchText"),"'",""))

'Response.Write "Start<br> " &strSearchText& "<BR>Done<BR><BR><BR>"

'Prune extra spaces
Do While InStr(strSearchText, " ")
strSearchText = Replace(strSearchText, " ", " ")

'Duplicate for display use
strText = strSearchText

'convert to lowercase to match case of the Stop List. Add leading and trailing spaces
strSearchText = LCase(" " & strSearchText & " ")

'Response.Write "Start<br> " &strSearchText& "<BR>Done<BR><BR><BR>"

'Go through the stop list to remove characters as needed
For intLoopControl = 0 To UBound(strStopList)
'Response.Write strSearchText & "<BR>"
strSearchText = Replace(strSearchText, " " &strStopList(intLoopControl)& " "," ")

'final trim
strSearchText = trim(strSearchText)

'Check for input text
if strSearchText <> "" Then

'split the input text into an array
arrWords = Split(strSearchText," ")

As you can see, I parse the input string to strip excess white space, and a stop list, to remove common and invalid words/letters.

I then generate the SQL search qeury, using the method on your site [url][/url] to do paging of search results.

Edited by - NickR on 03/19/2003 08:33:57

Edited by - NickR on 03/19/2003 08:35:30
Go to Top of Page

- Advertisement -