Share via

CONTAINS Predicate in Enterprise Search SQL Syntax

  • Article

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

…CONTAINS([<full-text_column>,]'<contains_condition>'[,<LCID>])…

Remarks

The CONTAINS predicate has features for matching words, matching inflectional forms of words, searching using wildcard characters, and searching using proximity. The CONTAINS predicate is better suited for exact matches, in contrast to the FREETEXT predicate, which is better suited to finding documents containing combinations of the search words spread throughout the column.

The full-text column reference is optional. With it, you can specify a single column or a column group against which the CONTAINS predicate is tested. When the full-text column is specified as "ALL" or "*", 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 using a comma. If you do not specify a full-text column, 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 CONTAINS 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 CONTAINS clause. For important information about searching and languages, see "Using Localized Searches" in Enterprise Search SQL Query Language Information.

Note

The default search locale is the system default locale.

You must enclose the CONTAINS condition portion in single quotation marks for single words, or double quotation marks for phrases, and it must be composed of of one or more content search terms that are combined by using the logical operators AND or OR. You can use the optional unary operator NOT to negate the logical value of a content search term.

NOT can occur only after AND. You cannot use the NOT operator if there is only one match condition, or after the OR operator.

You can use parentheses to group and nest content search terms. The following table describes the logical operator precedence order.

Order (Precedence) Logical Operator

First (highest)

NOT

Second

AND

Third (lowest)

OR

Logical operators of the same type are associative, and there is no specified calculation order. For example, (A AND B) AND (C AND D) can be calculated (B AND C) AND (A AND D), with no change in the logical result.

The following table describes the types of content search terms.

Type Description Examples

Word

A single word without spaces or other punctuation. Double quotation marks are not necessary.

…WHERE CONTAINS ('computer')

Phrase

Multiple words or included spaces.

…WHERE CONTAINS

('computer software')

Or, to include double quotation marks:

… WHERE CONTAINS('computer ""science""')

Wildcard

Words or phrases with the asterisk (*) added to the end. For more information, see Using Wildcards in the CONTAINS Predicate in Enterprise Search SQL Syntax.

…WHERE CONTAINS ('"compu*"')

Matches "computer", "computers", "computation", and "compulsory".

Boolean

Words, phrases, and wildcard strings combined by using the Boolean operators AND, OR, or NOT. Enclose the Boolean terms in double quotation marks.

…WHERE CONTAINS ('computer monitor' AND 'software program'

AND 'install component')

… WHERE CONTAINS ('computer'

AND 'software' AND 'install')

…WHERE CONTAINS ('computer software install')

Near

Words, phrases, or wildcards separated by the function NEAR. For more information, see NEAR Term in Enterprise Search SQL Syntax.

…WHERE CONTAINS

('computer' NEAR 'software')

FormsOf

Matches a word and the inflectional versions of that word. For more information, see FORMSOF Term in Enterprise Search SQL Syntax.

…WHERE CONTAINS ('FORMSOF

(INFLECTIONAL, "happy"))

Matches "happy", "happier", "happiest", "happily", and so on.

IsAbout

Combines matching results over multiple words, phrases, or wildcard search terms. For more information, see ISABOUT Term in Enterprise Search SQL Syntax.

…WHERE CONTAINS ('ISABOUT ( "computer","software","development") ')

In this Section

Noise Words and the CONTAINS Predicate in Enterprise Search SQL Syntax

Using Wildcards in the CONTAINS Predicate in Enterprise Search SQL Syntax

FORMSOF Term in Enterprise Search SQL Syntax

ISABOUT Term in Enterprise Search SQL Syntax

RANKMETHOD Term in Enterprise Search SQL Syntax

NEAR Term in Enterprise Search SQL Syntax

See Also

Reference

Full-Text Predicates in Enterprise Search SQL Syntax
WHERE Clause in Enterprise Search SQL Syntax