FREETEXT Predicate
FREETEXT Predicate
The FREETEXT predicate is part of the WHERE clause, and supports searching for words and phrases in text columns. The FREETEXT predicate is better suited for finding documents containing combinations of the search terms spread throughout the column, in contrast to the CONTAINS predicate, which is better suited for finding "exact" matches. You can also perform exact-phrase matching with the FREETEXT predicate by placing the phrase in double quotation marks.
The following example shows the 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 will be tested. When the fulltext_column is specified as "ALL" or "*", all text properties stored in the index 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 it must be separated from the condition by a comma. If no fulltext_condition is supplied, the Content column is used.
The fulltext_column reference can be specified 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 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.
The freetext_condition portion must be surrounded by single quotation marks, and consists of one or more content_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 searching, the FREETEXT predicate locates documents containing one or more of the content_search_terms. The terms need not appear in any particular order. Documents that contain more of the terms than other documents 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' )
You cannot use both single word and phrase matching in the same FREETEXT predicate. You can use multiple FREETEXT predicates to perform that function. The following example searches for documents containing both the word "computer" and the phrase "software development kit":
WHERE FREETEXT ( 'computer ') ANDFREETEXT (' "software development kit" ' )
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 will fail.
WHERE FREETEXT ( *,'"We'll always have Paris"' )
The correct syntax includes two single quotations, not a double quotation. For example, the following syntax will succeed.
WHERE FREETEXT ( *,'"We''ll always have Paris"' )
This section includes the following topic: