SQL Server - Fulltext search for %book%

Question

Folks,

I'm trying to wrap my head around how to search for something that appears in the middle of a word / expression - something like searching for "LIKE %book% " - but in SQL Server (2005) fulltext.

How can I do that?? It almost appears as if both CONTAINS and FREETEXT really don't support wildcard at the beginning of a search expression - can that really be???

I would have imagined that FREETEXT(*, "book") would find anything with "book" inside, including "rebooked" or something like that. No??? REALLY????

Cheers! Marc

Answer 1

If you want to do some serious full text searching then I would (and have) use Lucene.Net. MS SQL Full Text search never seems to work that well for anything other than the basics.

Answer 2

unfortunately CONTAINS only supports prefix wildcards:

CONTAINS(*, '"book*"')

Answer 3

SQL Server Full Text Search is based on tokenizing text into words. There is no smaller unit as a word, so the smallest things you can look for are words.

You can use prefix searches to look for matches that start with certain characters, which is possible because word lists are kept in alphabetical order and all the Server has to do is scan through the list to find matches.

To do what you want a query with a LIKE '%book%' clause would probably be just as fast (or slow).

Answer 4

Check this link for a helper class that is very useful for FTS on Sql Server 2005:

http://ewbi.blogs.com/develops/2007/05/normalizing_sql.html