June 2, 2006 at 3:30 pm
Hi,
I have a search option on our discussion forum webpage to allow users to search all the messages posted. It is very simple, no non-alphabet characters are allowed. Users can enter any search term and select one of two options - match all words in the search term or match any word in the search term.
This is the query that will be executed to retrieve the results if someone searches for 'sql server central'.
/*ALL WORDS*/
SELECT db.messageid, db.subject, db.postingdate
FROM discussionboard db
WHERE db.message like '%sql%' AND db.message like '%server%' AND db.message like '%central%'
/*ANY WORD*/
SELECT db.messageid, db.subject, db.postingdate
FROM discussionboard db
WHERE db.message like '%sql%' OR db.message like '%server%' OR db.message like '%central%'
We receive complaints every once in a while from customers saying the search function is not adequate. I tried microsoft indexing service long time back, it was a nightmare to get it set up, I finally gave up. Now I am thinking of making this search function better but I don't want to spend a great deal of time on this. Any suggestions?
Thanks.
June 2, 2006 at 5:17 pm
What improvements do they want?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 3, 2006 at 12:55 am
You are right, MS Search can be a pain but has been made a bit easier maintenance-wise in 2005.
Perhaps your users want to be able to use surrounding quotes - you can parse their quotes and send the entire quoted string to a single like comparison to make this work.
I would expect your users might like to be able to search for comput% to get computer, compute, computers, computation, computations, etc. If you do get such a search request, make sure you don't append your extra % at the end.
But as Tim says, what else do they want?
June 3, 2006 at 3:40 am
The full text search capabilities in SQL Server offer a reasonably wide range of functionality, which could perhaps be set up as a basic system and subsequently improved in response to specific user input, after checking for unwanted effects.
The last thing you want to do is spend time on setting up a new system only to find that no-one wants the new functionality, or even worse, that they 'need' the old system back.
BTW, you wouldn't want to pass strings straight through without checking them, anyway, but 'comput%' and 'comput%%' are equivalent, so adding anextra % wouldn't be such a big problem.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 4, 2006 at 4:29 pm
Like Ian mentioned, they want to be able to use quotes, return variations of the word, etc, just like a google search or close to it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply