SQL Server Full Text Search

Performing a Full-Text Search

Now you're ready to actually do some searches. For these examples, I added a full-text index to the ProductName column in the Northwind Products table. Four T-SQL predicates are involved in full-text searching:

• FREETEXT
• FREETEXTTABLE
• CONTAINS
• CONTAINSTABLE

FREETEXT is the easiest of these to work with; it lets you specify a search term but then tries to look at the meaning rather than the exact term when finding matches. For instance, here's a query using FREETEXT together with its results:

SELECT ProductNameFROM ProductsWHERE FREETEXT (ProductName, 'spread' )ProductName                              ---------------------------------------- Grandma's Boysenberry SpreadVegie-spread(2 row(s) affected)

As you can see, FREETEXT finds the word or words you give it anywhere in the search column. FREETEXTTABLE works like FREETEXT except that it returns its results in a Table object.

CONTAINS (and CONTAINSTABLE, which works the same but delivers results in a table) offers a much more complex syntax for using a full-text indexed column:

For instance, you can search for one word "near" another this way:

SELECT ProductNameFROM ProductsWHERE CONTAINS(ProductName, '"laugh*" NEAR lager')ProductName                              ---------------------------------------- Laughing Lumberjack Lager(1 row(s) affected)
Note the use of "laugh*" to match any word starting with "laugh." You can also supply a weighted list of terms to CONTAINS, and it will prefer matches with a higher weight:
SELECT ProductNameFROM ProductsWHERE CONTAINS(ProductName, 'ISABOUT (stout weight (.8),    ale weight (.4), lager weight (.2) )' )ProductName                              ---------------------------------------- Laughing Lumberjack LagerSteeleye StoutSasquatch AleOutback Lager(4 row(s) affected)