Top

Name

contains — A text contains predicate

Synopsis

boolean contains ( column varchar ,
  expression varchar ,
  opt_or_value integer ,
  ...);  
 

Description

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.

The score_limit is 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 a 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 modes.

Parameters

column

The table column whose contents are free text indexed

expression

A string matching the grammar of a text search expression.

opt_or_value

May be one or more of the following:

DESCENDING

specifies that the search will produce the hit with the greatest id first, as defined by integer or composite collation.

START_ID ',' scalar_exp

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.

END_ID ',' scalar_exp

the last allowed id in the traversal order. For descending order the START_ID must be >= END_ID for hits to be able to exist. For ascending order the START_ID must be <= END_ID for hits to be able to exist.

SCORE_LIMIT ',' scalar_exp

Minimum score that hits must have or exceed to be considered matches of the predicate.

RANGES ',' scalar_exp

specifies that the query variable following the RANGES keyword 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.

OFFBAND ',' column

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.

Return Types

The contains is a predicate, therefore returning a boolean .

Examples

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

    '"sql interfac*"'

  • Match documents with words beginning with 'dragon' and not containing the phrase 'once upon a time'. 

    '"dragon*" and not "once upon a time"'

[Note] Note:

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.

Prev  Up  Next
connection_vars_set   Home  cov_load