contains — A text contains predicate
||column varchar ,|
|expression varchar ,|
|opt_or_value integer ,|
This is a SQL predicate that specifies a condition on a column on which a free text index exists. The expression is a string matching the grammar of a text search expression. This is computed for each evaluation of the contains predicate and does not have to be a constant. For example a parameter or variable of a containing score (e.g. procedure) is accepted.
optional. If specified, it should be a numeric expression
determining the minimum score required to produce a hit.
A virtual column named 'SCORE' is available in queries involving
contains predicate. This can for
example be returned in a result set or used for sorting. Note that
the name is in upper case and is case sensitive in all case
The table column whose contents are free text indexed
A string matching the grammar of a text search expression.
May be one or more of the following:
specifies that the search will produce the hit with the greatest id first, as defined by integer or composite collation.
the first allowed document id to be selected by the expression in its traversal order, e.g. least or equal for ascending and greatest or equal for descending.
the last allowed id in the traversal order. For descending order the
START_IDmust be >=
END_IDfor hits to be able to exist. For ascending order the
START_IDmust be <=
END_IDfor hits to be able to exist.
Minimum score that hits must have or exceed to be considered matches of the predicate.
specifies that the query variable following the
RANGESkeyword will be bound to the word position ranges of the hits of the expression inside the document. The variable is in scope inside the enclosing SELECT statement.
Specifies that the following column will be retrieved from the free text index instead of the actual table. For this to be possible the column must have been declared as offband with the CLUSTERED WITH option of the CREATE TEXT INDEX statement.
The contains is a predicate, therefore returning a boolean .
Example 24.55. Querying Free Text Indexed Columns using contains()
Return the number of documents with one or more occurrences of "virtual" immediately followed by "database".
select count (*) from docs where contains (text, '"virtual database"')
Specify documents with performance and either 'tuning' or optimization' in any respective positions.
'performance and (tuning or optimization)'
Match documents with the word graphics more than 100 words away from 'user' or 'interface'.
'graphics and not (graphics near user near interface)'
Match documents with SQL followed by a word beginning with 'interfac'.
Match documents with words beginning with 'dragon' and not containing the phrase 'once upon a time'.
'"dragon*" and not "once upon a time"'
An expression may not consist of all negative terms, e.g. (not a) and (not b) is not a valid expression but 'c and not a and not b' is a valid expression.
Note that the NEAR connective may not be used between AND'ed or OR'ed terms. It can be used to combine words or phrases.