CONTAINS Predicate
CONTAINS Predicate
The CONTAINS predicate is part of the WHERE clause, and supports searching for words and phrases in text columns. The CONTAINS predicate has features for matching words, matching inflectional forms of words, searches using wildcards, and proximity searches. You can also apply weights in a CONTAINS predicate, to set the importance of the columns where the search term is found. 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 basic syntax of the CONTAINS predicate is:
… CONTAINS ( [ <fulltext_column> , ]
' <contains_condition> '
[, <LCID> ]
) …
The fulltext_column reference is optional. With it, you can specify a single column or a column group against which the CONTAINS predicate will be tested. When the fulltext_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 it must be separated from the condition by a comma. If no fulltext_column is specified, the Contents column, which is the body of the document, is used.
Note
Only the FREETEXT predicate supports column grouping and aliases by using the WITH…AS predicate. The CONTAINS predicate does not support column grouping and aliasing.
The fulltext_column reference can also be specified 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 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. Read the section Using Localized Searches for important information about searching and languages.
The contains_condition portion must be enclosed in single quotation marks for single words, double quotation for phrases, and consists of one or more content_search_terms, combined by using the logical operators AND or OR. The optional unary operator NOT can be used to negate the logical value of a content_search_term.
NOT can only occur after AND. The NOT operator cannot be used if there is only one match_condition, and NOT cannot be used after the OR operator.
You can use parentheses to group and nest content_search_terms. The following table shows 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 shows each content_search_term can be one of the types in.
Type | Description | Examples |
---|---|---|
Word | A single word without spaces or other punctuation. Double quotation marks are not necessary. |
|
Phrase | Multiple words or including spaces. |
|
Wildcard | Words or phrases with the asterisk (*) added to the end. For more information, see Using Wildcards in the CONTAINS Predicate. |
|
Boolean | Words, phrases, and wildcard strings combined by using the Boolean operators AND, OR, and NOT. Enclose the Boolean terms in double quotation marks. |
|
Near | Words, phrases, or wildcards separated by the function NEAR. For more information, see NEAR Term. |
|
FormsOf | Matches a word and the inflectional versions of that word. For more information, see FORMSOF Term. |
|
IsAbout | Combines matching results over multiple word, phrase, or wildcard search terms. Each search term can optionally be weighted. You can optionally specify the rank calculation method, which combines the weights and how many of the items the document matches. For more information, see ISABOUT Term. |
|
This section includes the following topics:
- Noise Words and the CONTAINS Predicate
- Using Wildcards in the CONTAINS Predicate
- FORMSOF Term
- ISABOUT Term
- RANKMETHOD Term
- NEAR Term