SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

AskSQLTeam
Ask SQLTeam Question

USA
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.

Tim
Starting Member

Australia
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

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

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

It's in my profile........here is a hint...it's cool!





Damian
Go to Top of Page

GreatInca
Posting Yak Master

USA
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

stakadush
Starting Member

Israel
2 Posts

Posted - 01/24/2002 :  20:23:03  Show Profile  Reply with Quote
Hey
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

MichaelP
Jedi Yak

USA
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"
MichaelP



Go to Top of Page

Onamuji
Aged Yak Warrior

USA
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

roblasch
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

NickR
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, " ", " ")
Loop

'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)& " "," ")
Next

'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 http://www.sqlteam.com/item.asp?ItemID=566 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  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000