FREETEXT
Is a predicate used to search columns containing character-based data types for values that match the meaning and not the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally "word-breaks" the freetext_string into a number of search terms and assigns each term a weight and then finds the matches.
Syntax
FREETEXT ( { column | * } , 'freetext_string' )
Arguments
column
Is the name of a specific column that has been registered for full-text searching. Columns of the character string data types are valid columns for full-text searching.
*
Specifies that all columns that have been registered for full-text searching should be used to search for the given freetext_string.
freetext_string
Is text to search for in the specified column. Any text, including words, phrases or sentences, can be entered. There is no concern about syntax.
Remarks
Full-text queries using FREETEXT are less precise than those full-text queries using CONTAINS. The Microsoft® SQL Server™ full-text search engine identifies important words and phrases. No special meaning is given to any of the reserved keywords or wildcard characters that typically have meaning when specified in the <contains_search_condition> parameter of the CONTAINS predicate.
FREETEXT is not recognized as a keyword if the compatibility level is less than 70. For more information, see sp_dbcmptlevel.
Examples
A. Use FREETEXT to search for words containing specified character values
This example searches for all product categories containing the words related to bread, candy, dry, and meat in the product description, such as breads, candies, dried, and meats.
USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE FREETEXT (Description, 'sweetest candy bread and dry meat' )
GO
B. Use variables in full-text search
This example uses a variable instead of a specific search term.
USE pubs
GO
DECLARE @SearchWord varchar(30)
SET @SearchWord ='Moon'
SELECT pr_info FROM pub_info WHERE FREETEXT(pr_info, @SearchWord)