CONTAINS Conditions

Both methods return the same rows; however, the CONTAINS clause also returns scores for the matching rows.

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 )
	| proximity-expression

fuzzy-expression:
	term
	| fuzzy-expression term

term:
	simple-term
	| prefix-term

prefix-term: 
	simple-term*

phrase:
	" phrase-string "

proximity-expression:
	term ( BEFORE | NEAR) [minimum distance, | maximum distance ]
 term
	| term { BEFORE | NEAR | ~ } term

phrase-string:
	term
	| phrase-string term

Parameters

simple-term – a string separated by white space and special characters that represents a single indexed term (word) for which to search.

distance – a positive integer.

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

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. When you use a hyphen for negation, a space must precede the hyphen, and the hyphen must abut the subsequent term. 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"'.

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

fuzzy-expression – use fuzzy-expression to find terms that are similar to what you specify. Fuzzy matching is only supported on NGRAM TEXT indexes.

proximity-expression – use proximity-expression to search for terms that are near each other. For example, 'b NEAR[2,5] c' searches for instances of b and c that are at most five and at least 2 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').

BEFORE is like NEAR, except that the order of terms is significant. 'b BEFORE c' is not equivalent to 'c BEFORE b'; in the former, the term 'b' must precede 'c' while in the latter the term 'b' must follow 'c'. BEFORE accepts both minimum and maximum distances like NEAR. The default minimum distance is 1. The minimum distance, if given, must be less than or equal to the maximum distance; otherwise, an error is returned.

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.

Usage

The CONTAINS search condition takes a column list and contains-query-string as arguments.

The CONTAINS search condition 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. You can reference the base directly in the FROM clause, or use it 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.

Queries using ANSI join syntax are supported (FULL OUTER JOIN, RIGHT OUTER JOIN, LEFT OUTER JOIN), but may have suboptimal performance. Use outer joins for CONTAINS in the FROM clause only if the score column from each of the CONTAINS clauses is required. Otherwise, move CONTAINS to an ON condition or WHERE clause.

If you use a SQL variable less than 32KB in length as a search term and the type of variable is LONG VARCHAR, use CAST to convert the variable to VARCHAR data type. For example:

SELECT * FROM tab1 WHERE CONTAINS(c1, cast(v1 AS VARCHAR(64))

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 white space and serve as term breakers.