CONTAINS search condition

Syntax

CONTAINS ( column-name [,...], contains-query-string )

contains-query-string :
simple-expression
| or-expression

simple-expression :
primary-expression
| and-expression

or-expression :
simple-expression { OR | | } contains-query-string

primary-expression :
basic-expression
| FUZZY " fuzzy-expression "
| and-not-expression

and-expression :
primary-expression [ AND | & ] simple-expression

and-not-expression :
primary-expression [ AND | & ] { NOT | - } basic-expression

basic-expression :
term
| phrase
| ( contains-query-string )
| near-expression

fuzzy-expression :
term
| fuzzy-expression term

term :
simple-term
| prefix-term

prefix-term : 
simple-term*

phrase :
" phrase-string "

near-expression :
term NEAR[distance] term
| term { NEAR | ~ } term

phrase-string :
term
| phrase-string term

simple-term : A string separated by whitespace and special characters that
represents a single indexed term (word) to search for.

distance : a positive integer

Parameters

and-expression Use and-expression to specify that both primary-expression and simple-expression must be found in the text index.
By default, if no operator is specified between terms or expressions, an and-expression is assumed. For example, 'a b' is interpreted as 'a AND b'.

An ampersand (&) can be used instead of AND, and can abut the expressions or terms on either side (for example, 'a &b').

See Allowed syntax for special characters.
and-not-expression Use and-not-expression to specify that primary-expression must be present in the text index, but that basic-expression must not be found in the text index. This is also known as a negation.
If you use a hyphen for negation, the hyphen must have a space to the left of it, and must abut the term to the right; otherwise, the hyphen is not interpreted as a negation. For example, 'a -b' is equivalent to 'a AND NOT b'; whereas for 'a - b', the hyphen is ignored and the string is equivalent to 'a AND b'. 'a-b' is equivalent to the phrase '"a b"'. See Allowed syntax for hyphen (-).
or-expression Use or-expression to specify that at least one of simple-expression or contains-query-string must be present in the text index. For example, 'a|b' is interpreted as 'a OR b'. See Allowed syntax for special characters.
fuzzy-expression Use fuzzy-expression to find terms that are similar to what you specify. Fuzzy matching is only supported on NGRAM text indexes. See Fuzzy searches.
near-expression Use near-expression to search for terms that are near each other. This is also known as a proximity search. For example, 'b NEAR[5] c' searches for instances of b and c that are five or less terms away from each other. The order of terms is not significant; 'b NEAR c' is equivalent to 'c NEAR b'.
If NEAR is specified without distance, a default of 10 terms is applied.

You can specify a tilde (~) instead of NEAR. This is equivalent to specifying NEAR without a distance so a default of 10 terms is applied.

NEAR expressions cannot be chained together (for example, 'a NEAR[1] b NEAR[1] c').

See Allowed syntax for special characters, and Proximity searching.
prefix-term Use prefix-term to search for terms that start with the specified prefix. For example, 'datab*' searches for any term beginning with datab. This is also known as a prefix search. In a prefix search, matching is performed for the portion of the term to the left of the asterisk. See Allowed syntax for asterisk (*), and Prefix searching.

Remarks

The CONTAINS search condition takes a column list and contains-query-string as arguments. It can be used anywhere a search condition (also referred to as predicate) can be specified, and returns TRUE or FALSE. contains-query-string must be a constant string, or a variable, with a value that is known at query time.

If multiple columns are specified, then they must all refer to a single base table; a text index cannot span multiple base tables. The base table can be referenced directly in the FROM clause, or it can used in a view or derived table provided that the view or derived table does not use DISTINCT, GROUP BY, ORDER BY, UNION, INTERSECT, EXCEPT, or a row limitation.

The following warnings apply to the use of non-alphanumeric characters in query strings:

An asterisk in the middle of a term returns an error.
You should not use non-alphanumerics (including special characters) in fuzzy-expression because they are treated as whitespace and serve as term breakers.
If possible, do not include non-alphanumeric characters that are not special characters in your query string. Any non-alphanumeric character that is not a special character causes the term containing it to be treated as a phrase, breaking the term at the location of the character. For example, 'things we've done' is interpreted as 'things "we ve" done'.

Within phrases, the asterisk is the only special character that continues to be interpreted as a special character. All other special characters within phrases are treated as whitespace and serve as term breakers.

Interpretation of contains-query-string takes place in two main steps:

Step 1: Interpreting operators and precedence During this step, keywords are interpreted as operators, and rules of precedence are applied. See Operator precedence in a CONTAINS search condition.
Step 2: Applying text configuration object settings During this step, the text configuration object settings are applied to terms. For example, on an NGRAM text index, terms are broken down into their n-gram representation. During this step, the query terms that exceed the term length settings, or that are in the stoplist, are dropped. For more information about how a query string is interpreted when terms are dropped, see Example text configuration objects.

Operator precedence in a CONTAINS search condition

During query evaluation, expressions are evaluated using the following order of precedence:

FUZZY, NEAR
AND NOT
AND
OR

Allowed syntax for asterisk (*)

The asterisk is used for prefix searching. An asterisk can occur at the end of the query string, or be followed by a space, ampersand, vertical bar, closing bracket, or closing quotation mark. Any other usage of asterisk returns an error.

The following table shows allowable asterisk usage:

Query string	Equivalent to:	Interpreted as:
`'th*'`		Find any term beginning with th.
`'th*&best'`	`'th* AND best'` and `'th* best'`	Find any term beginning with th, and the term best.
`'th*\|best'`	`'th* OR best'`	Find either any term beginning with th, or the term best
`'very&(best\|th*)'`	`'very AND (best OR th*)'`	Find the term very, and the term best or any term beginning with th.
`'"fast auto*"'`		Find the term fast, immediately followed by a term beginning with auto.
`'"auto* price"'`		Find a term beginning with auto, immediately followed by the term price.

Note

Interpretation of query strings containing asterisks can vary depending on the text configuration object settings. See Prefix searching.

Allowed syntax for hyphen (-)

The hyphen can be used for term or expression negation, and is equivalent to NOT. Whether a hyphen is interpreted as a negation depends on its location in the query string. For example, when a hyphen immediately precedes a term or expression it is interpreted as a negation. If the hyphen is embedded within a term, it is interpreted as a hyphen.

A hyphen used for negation must be preceded by a whitespace, and followed immediately by an expression.

When used in a phrase of a fuzzy expression, the hyphen is treated as whitespace and used as a term breaker.

The following table shows the allowed syntax for hyphen:

Query string	Equivalent to:	Interpreted as:
`'the -best'`	`'the AND NOT best'`, `'the AND -best'`, `'the & -best'`, `'the NOT best'`	Find the term the, and not the term best.
`'the -(very best)'`	`'the AND NOT (very AND best)'`	Find the term the, and not the terms very and best.
`'the -"very best"'`	`'the AND NOT "very best"'`	Find the term the, and not the phrase very best.
`'alpha-numerics'`	`'"alpha numerics"'`	Find the term alpha, immediately followed by the term numerics.
`'wild - west'`	`'wild west'`, and `'wild AND west'`	Find the term wild, and the term west.

Allowed syntax for special characters

The following table shows the allowed syntax for all special characters except asterisk and hyphen, which are covered in the previous sections: Allowed syntax for asterisk (*), and Allowed syntax for hyphen (-).

These characters are not considered special characters if they are found in a phrase, and are dropped.

Note

The same restrictions with regards to specifying string literals also apply to the query string. For example, apostrophes must be escaped, and so on. For more information on formatting string literals, see String literals.

Character or syntax	Usage Examples and remarks
ampersand (&)	The ampersand is equivalent to AND, and can be specified as follows: `'a & b'` `'a &b'` `'a&b'` `'a& b'`
vertical bar (\|)	The vertical bar is equivalent to OR, and can be specified as follows: `'a\|b'` `'a \|b'` `'a \| b'` `'a\| b'`
double-quotes (")	Double-quotes are used to contain a sequence of terms where order and relative distance are important. For example, in the query string `'learn "full text search"'`, "full text search" is a phrase. In this example, learn can come before or after the phrase, or exist in another column (if the text index is built on more than one column), but the exact phrase must be found in a single column.
parentheses ()	Parentheses are used to specify the order of evaluation of expressions if different from the default order. For example `'a AND (b\|c)'` is interpreted as a, and b or c. For more information about the default order of evaluation, see Operator precedence in a CONTAINS search condition.
tilde (~)	The tilde is equivalent to NEAR, and has no special syntax rules. The query string `'full~text'` is equivalent to `'full NEAR text'`, and is interpreted as: the term full within ten terms of the term text.
square brackets [ ]	Square brackets are used in conjunction with the keyword NEAR to contain distance. Other uses of square brackets returns an error.