Normalizing search text to full text search and where conditions

Question

What you want is for users to just type in their search criteria just like they would in Google. Some words, maybe some quoted phrases, maybe a few operators, and have it just work.

A .Net solution is available here: http://ewbi.blogs.com/develops/2007/05/normalizing_sql.html

I am looking for a pure T-SQL version with where support also. (Or VbScript/javascript)

Example: "dog" food price:20..45

should look like this (for mssql): select * from table t join containstable(desc, '"dog" and food*') k on k.key=t.id where t.price between 20 and 45

Operators: and, or, near, "", not, * , etc.

Answer 1

I don't see how you could have this functionality short of writing a complete parser that is programmed with the table relationships and column datatypes that exist on your database.