Providing a "user-friendly" interface to fulltext search

Question

We use SQL Server fulltext search for several tables on our application. We always use AND searches, e.g.:

• "Evil" returns "Evil Dead" and "The Evil of Fu Manchu"
• "Evil Fu" returns only "The Evil of Fu Manchu"

We want to keep the interface down to just a single search box and button, and we don't want people to have to learn much (if any) in the way of special syntax. We use CONTAINS rather than FREETEXTTABLE because of the AND requirement.

The limitation is that CONTAINS does not seem to match synonyms. My question is really: does anyone out there have a pattern for "interpreting" user input to useful fulltext syntax?

Answer 1

If you had a synonym table you could look up a set of terms and run those through your CONTAINS query.

You could use something like the Big Huge Thesaurus API