Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
AskSQLTeam
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. |
|
stakadush
Starting Member
2 Posts |
Posted - 2002-01-24 : 20:23:03
|
HeyIs 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 :) |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-01-25 : 09:44:36
|
To make this accurate, remove the wildcards in this statementINNER 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 |
|
|
Onamuji
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? |
|
|
roblasch
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? |
|
|
NickR
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, " ", " ") 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 [url]http://www.sqlteam.com/item.asp?ItemID=566[/url] to do paging of search results.Edited by - NickR on 03/19/2003 08:33:57Edited by - NickR on 03/19/2003 08:35:30 |
|
|
|
|
|
|
|