May 31, 2005 at 5:10 pm
So the company I work for has been moving more of it's web content to a sql server database in order to have it be more dynamic. One of the newer tasks is to have the db be searchable kinda like a google search
things like "Machine +RPM -Small"
any ideas? the field being searched is an nText field.
-- Francisco
May 31, 2005 at 10:52 pm
I have not build something with all of these features, but having built a multiple word search, I parsed the input string into each of the words and loaded those into a temp variables and searched on each of the words. The down sides of this approach is it does not support all of the features that you were describing "+" and "-", and you need to limit the maximum number of words to process to the number of temp variables that you set-up.
The most important thing to remember when you start a project like this one is to make sure that you have the requirements defined well. Google has many developers working on this all the time for a long time to make the search capabilities broad.
June 1, 2005 at 2:47 am
It depends upon what front end you are going to allow the users to access the data from.
If you are using the web, you can create an asp page and use the vb script function SPLIT. This function will split words into an array depending upon the seperator used.
You can the build a sqlstring using like by looping through the code. A bit confusing so I will show you an excerpt from my own asp page:
---------------------------------------
' the array of all the terms searched for
a=Split(strWordSearch,",",-1,1)
For i = 0 to ubound(a)
a(i) = trim(a(i))
Next
strSQL = 'Select * from table where id > 0 '
For i = 0 to ubound(a)
strSQL = strSQL & " AND FULL_NAME LIKE '%" & Replace(a(i), "'", "''") & "%' "
Next
----------------------------------------
strWordSearch is the string taken from the input box. The split function creates an array called 'a' which I trim to take out leading spaces. This split function separates words using a comma (you can use what you like).
I then just loop through the array building up the sql string line by line. I replace single quotes to make sure the query parses successfully.
Hope this makes sense. Easy to use and dynamic.
June 1, 2005 at 9:40 am
Thanks for the replies. I found an article over on DatabaseJournal on Full Text Indexing, it looks like that just might be a step in the right direction, I'll continue posting my findings as I get them...
For now, this is the link on how to set up Full Text Indexing
http://www.databasejournal.com/features/mssql/article.php/1438211
and how to begin using it...
http://www.databasejournal.com/features/mssql/article.php/3441981
-- Francisco
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply