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.

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

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

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

1. FUZZY, NEAR

2. AND NOT

3. AND

4. OR

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 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 white space, and followed immediately by an expression.

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

TEXT indexes are built according to the settings defined for the text configuration object used to create the TEXT index. ATEXT index excludes terms that meet any of the following conditions:

• The term is included in the stop list.

• The term is shorter than the minimum term length (GENERIC only).

• The term is longer than the maximum term length.

The same rules apply to query strings. The dropped term can match zero or more terms at the end or beginning of the phrase. For example, suppose the term 'the' is in the stop list:

• If the term appears on either side of an AND, OR, or NEAR, then both the operator and the term are removed. For example, searching for 'the AND apple', 'the OR apple', or 'the NEAR apple' are equivalent to searching for 'apple'.

• If the term appears on the right side of an AND NOT, both the AND NOT and the term are dropped. For example, searching for 'apple AND NOT the' is equivalent to searching for 'apple'.

• If the term appears on the left side of an AND NOT, the entire expression is dropped. For example, searching for 'the AND NOT apple' returns no rows. Another example: 'orange and the AND NOT apple' is the same as 'orange AND (the AND NOT apple)' which, after the AND NOT expression is dropped, is equivalent to searching for 'orange'. Contrast this with the search expression '(orange and the) and not apple', which is equivalent to searching for 'orange and not apple'.

• If the term appears in a phrase, the phrase is allowed to match with any term at the position of the dropped term. For example, searching for 'feed the dog' matches 'feed the dog', 'feed my dog', 'feed any dog', and so on.

When you include a CONTAINS clause in the FROM clause of a query, each match has a score associated with it. The score indicates how close the match is, and you can use score information to sort the data. Two main criteria determine score:

• The number of times a term appears in the indexed row. The more times a term appears in an indexed row, the higher its score.

• The number of times a term appears in the TEXT index. The more times a term appears in a TEXT index, the lower its score.

Depending on the type of full text search, other criteria affect scoring. For example, in proximity searches, the proximity of search terms impacts scoring. By default, the result set of a CONTAINS clause has the correlation name contains that has a single column in it called score. You can refer to "contains".score in the SELECT list, ORDER BY clause, or other parts of the query. However, because contains is a SQL reserved word, you must remember to put it in double quotes. Alternatively, you can specify another correlation name, for example, CONTAINS ( expression ) AS ct. The examples for full text search refer to the score column as ct.score.

This statement searches MarketingInformation.Description for terms starting with ‘stretch” or terms starting with “comfort”:

SELECT ID, ct.score, Description
FROM MarketingInformation
CONTAINS ( MarketingInformation.Description,
           'stretch* | comfort*' )
AS ct ORDER BY ct.score DESC;