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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Keyword search using a tally or sequence table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 10/07/2001 :  20:01:33  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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

392 Posts

Posted - 10/07/2001 :  20:11:08  Show Profile  Reply with Quote
Merkin can you tell us the addressof your "similar site", I am curious to take a look see.

Visit nr's cursor wonderland
Go to Top of Page

Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 10/07/2001 :  20:18:03  Show Profile  Visit Merkin's Homepage  Reply with Quote

It's in my is a's cool!

Go to Top of Page

Posting Yak Master

102 Posts

Posted - 10/07/2001 :  21:28:53  Show Profile  Visit GreatInca's Homepage  Send GreatInca an AOL message  Send GreatInca a Yahoo! Message  Reply with Quote
How does the performance of this compare to using full text indexing?

Go to Top of Page

Starting Member

2 Posts

Posted - 01/24/2002 :  20:23:03  Show Profile  Reply with Quote
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 - 01/25/2002 :  09:44:36  Show Profile  Visit MichaelP's Homepage  Reply with Quote
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 - 06/03/2002 :  08:59:52  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
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 - 09/06/2002 :  19:23:07  Show Profile  Reply with Quote
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 Posts

Posted - 03/19/2003 :  08:28:20  Show Profile  Reply with Quote
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 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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000