Converting user-entered search query to where clause for use in SQL Server full-text search

Question

What's the best way to convert search terms entered by a user, into a query that can be used in a where clause for full-text searching to query a table and get back relevant results? For example, the following query entered by the user:

+"e-mail" +attachment -"word document" -"e-learning"

Should translate into something like:

SELECT * FROM MyTable WHERE (CONTAINS(*, '"e-mail"')) AND (CONTAINS(*, '"attachment"')) AND (NOT CONTAINS(*, '"word document"')) AND (NOT CONTAINS(*, '"e-learning"'))

I'm using a query parser class at the moment, which parses the query entered by users into tokens using a regular expression, and then constructs the where clause from the tokens.

However, given that this is probably a common requirement by a lot of systems using full-text search, I'm curious as to how other developers have approached this problem, and whether there's a better way of doing things.

Answer 1

Watch out for SQL injection

Answer 2

I realize it's a bit of a side-step from your original question, but have you considered moving away from SQL fulltext indexes and using something like Lucene/Solr instead?

Answer 3

This may not be exactly what you are looking for but it may offer you some further ideas.

http://www.sqlservercentral.com/articles/Full-Text+Search+(2008)/64248/

Answer 4

The easiest way to do this is to use dynamic SQL (I know, insert security issues here) and break the phrase into a correctly formatted string.

You can use a function to break the phrase into a table variable that you can use to create the new string.

Answer 5

A combination of GoldParser and Calitha should sort you out here.

This article: http://www.15seconds.com/issue/070719.htm has a googleToSql class as well, which does some of the translation for you.