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 ProductName
FROM Products
WHERE FREETEXT (ProductName, 'spread' )

ProductName                              
---------------------------------------- 
Grandma's Boysenberry Spread
Vegie-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 ProductName
FROM Products
WHERE 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 ProductName
FROM Products
WHERE CONTAINS(ProductName, 'ISABOUT (stout weight (.8), 
   ale weight (.4), lager weight (.2) )' )

ProductName                              
---------------------------------------- 
Laughing Lumberjack Lager
Steeleye Stout
Sasquatch Ale
Outback Lager

(4 row(s) affected)