CONTAINS Conditions

Perform a full text query using the CONTAINS clause in the FROM clause of a SELECT statement, or by using the CONTAINS search condition (predicate) in a WHERE clause.

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 – 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 – 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 – 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 – to find terms that are similar to what you specify. Fuzzy matching is only supported on NGRAM TEXT indexes.
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 – 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.

These types of queries are unsupported:

Remote queries using a SQL Anywhere table with a full TEXT index that is joined to a remote table.
Queries using SAP Sybase IQ and SQL Anywhere tables, where the full TEXT index to be used is on the SQL Anywhere table.
Queries using TSQL style outer join syntax (*=*, =* and *=).

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

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.
Avoid using non-alphanumeric characters (including special characters) in fuzzy-expression, because they are treated as white space and serve as term breakers.
If possible, avoid using 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 white space and serve as term breakers.

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

Step 1: Interpretation of operators and precedence: During this step, keywords are interpreted as operators, and rules of precedence are applied.
Step 2: Application of text configuration object settings: During this step, the text configuration object settings are applied to terms. Any query terms that exceed the term length settings, or that are in the stop list, are dropped.