FREETEXT Predicate
The FREETEXT predicate is part of the WHERE clause, and supports searching for words and phrases in text columns. Use the FREETEXT predicate to find documents containing combinations of the search words spread throughout the column. In contrast, use the CONTAINS predicate to find "exact" matches. You can also perform exact-phrase matching with the FREETEXT predicate by placing the phrase in double quotation marks.
Following is the basic syntax of the FREETEXT predicate:
FREETEXT
([<fulltext_column>,]'<freetext_condition>'[,<LCID>])…
The fulltext column reference is optional. With it, you can specify a single column, or a column grouping alias against which the FREETEXT predicate is tested. All indexed text properties are searched. Although the column is not required to be a text property, the results might be meaningless if the column is some other data type. The column name can be either a regular or delimited identifier, and you must separate it from the condition by a comma. If no fulltext condition is supplied, the Contents column, which is the body of the document, is used.
You can also specify the fulltext column reference with an asterisk (*), indicating all columns.
You can specify the search locale for the FREETEXT predicate. This instructs the search engine to use the appropriate word breaker, noise word list, inflectional forms, and sort order for the search query. To specify the locale, provide the Microsoft Windows standard locale identifier, also known as the LCID. For example, 1033 is the LCID for United States-English. Place the LCID as the last item inside the parentheses of the FREETEXT clause. For important information about searching and languages, see Using Localized Searches.
Note The default search locale is the system default locale.
You must enclose the freetext condition portion in single quotation marks, and it must consist of one or more search terms. The FREETEXT predicate does not support logical operations. To search for a phrase as if it were a single word, enclose the phrase in double quotation marks.
When you use the FREETEXT predicate, the search query results return documents containing one or more of the search terms. The terms do not need to appear in any particular order. Documents that contain more of the search terms may have higher rank column values.
Examples
The following example searches for documents containing "computer", "software", "hardware", or combinations of those words:
WHERE FREETEXT('computer software hardware')
Note You cannot use both single-word and phrase matching in the same FREETEXT predicate.
When performing queries with contractions, you must escape the quotation mark in the contraction when using FREETEXT, but not when using CONTAINS.
For example, the following syntax fails:
WHERE FREETEXT(*,'"We'll always have Paris"')
The correct syntax includes two single quotation marks, not a double quotation mark.
The following syntax succeeds:
WHERE FREETEXT(*,'"We''ll always have Paris"')
This section includes the following topic: