CONTAINS search condition

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

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.
If you use a hyphen for negation, the hyphen must have a space to the left of it, and must adjoin 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"'.
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.
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. You cannot specify a distance if you specify a tilde; it is always 10 terms.

NEAR expressions cannot be chained together (for example, 'a NEAR[1] b NEAR[1] c').
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.

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. The contains-query-string must be a constant string, or a variable, with a value that is known at query time. The contains-query-string cannot be NULL, an empty string, or exceed 300 valid terms. A valid term is a term that is within the permitted term length and is not included in the STOPLIST. An error is returned when the contains-query-string exceeds 300 valid terms.

If the text configuration settings cause all of the terms in the contains-query-string to be dropped, the result of the CONTAINS search condition is FALSE.

For more information about how the contains-query-string is interpreted, see Example text configuration objects.

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 be used in a view or derived table.

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

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

FUZZY, NEAR
AND NOT
AND
OR

SQL Anywhere does not currently support the BEFORE keyword as an operator. For example, if you specify CONTAINS(column-name, 'a before b'), an error is returned. Construct your query using the NEAR keyword instead.

You can search for the word "before", providing it is part of a phrase query. For example, CONTAINS(column-name, '"a before b"'). This searches for the phrase "a before b".

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.

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.

The following table shows the allowed syntax for all special characters except asterisk and 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.

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.
tilde (~)	The tilde is equivalent to NEAR[10]. 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. You cannot specify a distance with the tilde.
square brackets [ ]	Square brackets are used in conjunction with the keyword NEAR to contain distance. Other uses of square brackets returns an error.

SQL/2008 The CONTAINS predicate is a vendor extension.