FREETEXT Predicate in Windows SharePoint Services Search SQL Syntax

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Part of the WHERE clause that supports searching for words and phrases in text columns.

FREETEXT
([<full-text_column>,]'<freetext_condition>'[,<LCID>])…

Remarks

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.

The full-text column reference is optional. For relevance purposes, we recommend that you specify DEFAULTPROPERTIES here. For example:

…WHERE FREETEXT(DEFAULTPROPERTIES, 'computer software')

Alternatively, 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 full-text condition is supplied, the Contents column, which is the body of the document, is used.

You can also specify the full-text 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 of the parentheses of the FREETEXT clause. For important information about searching and languages, see "Using Localized Searches" in Windows SharePoint Services Search SQL Query Language Information.

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.

The default behavior of the FREETEXT predicate is that the query returns documents for which the following is true:

• The document contains all of the search terms in at least one of the columns specified.

• One of the search terms must also be found in the Contents column, which is the property containing the body of the document.

The terms do not need to appear in any particular order. Documents that contain more of the search terms might have higher rank column values.

The FREETEXT predicate also supports inclusion "+" and exclusion "-" characters, using the same matching rules as the Keyword syntax. For information about the matching rules, see Windows SharePoint Services Search Keyword Syntax Inclusion and Exclusion.

Examples

The following example searches for documents containing "computer", "software", and "hardware".

…WHERE FREETEXT('computer software hardware')

You can use single-word and phrase matching in the same FREETEXT predicate. The following example searches for documents containing the word "software", and the phrase "computer hardware".

…FREETEXT('software "computer hardware"')

When performing queries with contractions and using the FREETEXT predicate, you must escape the quotation mark in the contraction; you do not have to do this when using the CONTAINS predicate.

For example, the following syntax fails:

…WHERE FREETEXT(*,'"We'll always have Paris"')

The correct syntax includes two single quotation marks.

The following syntax succeeds:

…WHERE FREETEXT(*,'"We''ll always have Paris"')

The following example searches for documents that contain the search terms "hardware" and "store", and do not contain the search term "computer".

…WHERE FREETEXT(DEFAULTPROPERTIES, 'hardware store -computer')

The following example won't search for documents in which the author property contains the search term "John"; instead it searches for documents containing the search terms "author", "John", "computer", "hardware".

WHERE FREETEXT(DEFAULTPROPERTIES, 'author:John computer hardware')

The following example searches for documents in which the author property contains the search term "John", and the search terms "computer" and "hardware" somewhere else in the document.

…WHERE CONTAINS(author, 'John') AND FREETEXT(DEFAULTPROPERTIES, 'computer hardware')

In this Section

Noise Words and the FREETEXT Predicate in Windows SharePoint Services Search SQL Syntax

See Also

Reference

CONTAINS Predicate in Windows SharePoint Services Search SQL Syntax

WITH -- AS Group Alias Predicate in Windows SharePoint Services Search SQL Syntax