Edit

CONTAINS Predicate

  • Article

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, searching using wildcard characters, and searching using proximity. 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. Searches are not case-sensitive.

The following is the basic syntax of the CONTAINS predicate:

...CONTAINS(["<fulltext_column>",]'<contains_condition>'[,<LCID>])...

The fulltext_column reference is optional. With it, you can limit the search to a single column or a column group against which the CONTAINS predicate is 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 you must separate it from the condition by a comma. If no fulltext column is specified, the System.Search.Contents column, which is the body of the document, is used.

The LCID portion of the predicate specifies the search locale. This instructs the search engine to use the appropriate word breaker and inflectional forms for the search query. To specify the locale, provide the Windows standard language code identifier (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.

Note

The default search locale is the system default locale.

The contains_condition portion must be enclosed in single quotation marks for single words or double quotation marks for phrases, and consists of one or more content search terms that are combined using the logical operators AND or OR. You can use the optional unary operator NOT after an AND operator to negate the logical value of a content search term.

Note

The NOT operator 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 order of precedence for the logical operators.

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 use a double quote mark:


... 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. 

...WHERE CONTAINS
('"compu*"')

Matches "computer", "computers",
"computation", and "compulsory"
Full-text Column A property column name against which to match the remaining query. 
...WHERE CONTAINS (System.Author,'"James" OR "Juan"')
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. 
...WHERE CONTAINS
('"computer" NEAR "software"')
FormsOf Matches a word and the inflectional versions of that word. For more information, see FORMSOF Term. 

...WHERE CONTAINS
('FORMSOF
 (INFLECTIONAL, "happy"))

Matches "happy", "happier",
"happiest", "happily", and so on.
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. 
...WHERE CONTAINS
('ISABOUT ( "computer" WEIGHT (0.75) ,
    "software" WEIGHT (0.25) ,
    "development" WEIGHT (0.255)
 ) RANKMETHOD INNER PRODUCT
')

This section includes the following topics:

Reference

WHERE Clause

Conceptual

Full-Text Predicates

Non-Full-Text Predicates