# 22. Virtuoso Functions Guide - contains

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
Virtuoso Functions Guide
Administration
Aggregate Functions
Array Manipulation
BPEL APIs
Backup
Compression
Cursor
Date & Time Manipulation
Debug
Dictionary Manipulation
Encoding & Decoding
File Manipulation
Free Text
vt_batch_update
vt_drop_ftt
composite
composite_ref
contains
vt_batch
vt_batch_d_id
vt_batch_feed
vt_batch_feed_offban...
vt_create_text_index
vt_is_noise
Hashing / Cryptographic
LDAP
Locale
Mail
Miscellaneous
Number
Remote SQL Data Source
Replication
SOAP
SQL
String
Transaction
Type Mapping
UDDI
User Defined Types & The CLR
Virtuoso Java PL API
Virtuoso Server Extension Interface (VSEI)
Web Server & Internet
XML
XPATH & XQUERY
Functions Index

## contains

A text contains predicate
boolean contains ( column varchar, expression varchar, [opt_or_valueinteger]);
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.

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.

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

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.

See Also

xpath_contains()

The Free Text Index Chapter.

Other Free Text Functions.

Free Text Triggers.