SQL Server full text search

Faster 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

FREETEXTIs 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 usingFREETEXTTogether with its results:

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

As you can see,FREETEXTFinds the word or words you give it anywhere in the search column.FREETEXTTABLEWorks likeFREETEXTStatement t that it returns its results in a table object.

CONTAINS(AndCONTAINSTABLE, 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)