Omnidex Text

$CONTAINS

Omnidex specializes in textual searches and provides a rich suite of tools for locating rows containing specific keywords and their variations. $CONTAINS is an Omnidex extended function designed to overcome some of the limitations of standard SQL with respect textual searches.

The $CONTAINS function is used as a SELECT statement criteria predicate to allow complex qualification criteria against a text field using proximity, synonym, spell-checked and misspelling searches, as well as form and phonetic searches. $CONTAINS is also a prerequisite for using the $SCORE and $CONTEXT functions, which provide relevancy scoring and context excerpts.

The criteria in $CONTAINS apply to parsed words within a column and can include parenthetical and Boolean relationships between words. The $CONTAINS function is unusual in that it comprises an entire predicate rather than being used as a left or right predicate operand.

A $CONTAINS criteria predicate can be used to specify criteria for any MDK indexed column, even when no special text search functions are being performed. Additionally, QUALIFY syntax criteria can be passed in the criteria string. These are shown below in Examples.

A SELECT statement WHERE clause can contain multiple $CONTAINS functions, however, a label must be used to identify each predicate.

The $CONTAINS function returns a Boolean TRUE or FALSE.

Syntax

$CONTAINS(column, 'criteria'[, 'options'[, 'label']])

column
Required. The column against which the criteria should be applied. The column name may optionally be qualified by the table name or the table alias.

criteria
Required. Qualification criteria to be applied against the column. These criteria can include parentheses, qualification functions, Boolean operators and other textual operators described in this document. The entire criteria string must be enclosed in single quotes.

options
Optional. See Options (below) for a list of valid options. The entire options string must be enclosed in single quotes.

label
Optional. A label distinguishing this function from another, when multiple $CONTAINS functions are used in a single SELECT statement. This label is case-insensitive and may be up to 32 characters in length. The label must be enclosed in single quotes.

Options

Primary Options

SYNONYMS[=list]
Expand the search to include the synonyms of each word in criteria. If list is not provided, the use the list declared in the environment file for this column. If no list is provided in the environment file, then use the thesaurus.

SPELLCHECK[=list]
Check each word in criteria to see if it is a commonly misspelled word, and automatically replace the word with the correct spelling. If list is not provided, then use the list declared in the environment file for this column. If no list is provided in the environment file, then use the default list provided with Omnidex (recommended).

MISSPELLINGS
Locate possible misspellings for each word in criteria. If list is not provided, then use the spelling dictionaries created by ODXSQL's UPDATE TEXT command.

FORMS[=approach]
Expand the search to include the various forms of each word. Approach can be set to PLURAL, DERIVATIONS or both. If approach is not provided, then use the approach declared in the environment file for this column. If no approach is provided in the environment file, then PLURAL will be used by default.

PHONETIC
Expand the search to include the phonetic equivalents for each word. This option requires that the Phonetic indexing option (;SX - Soundex) be installed on this column.

STOPWORDS[=list]
Restrict this search by excluding all words found in the stopwords list. If list is not provided, then use the list declared in the environment file for this column. If no list is provided in the environment file, then use the default list provided with Omnidex.

Secondary Options

The secondary options configure the way that the primary options are applied:

INCLUSIVE
Search for the original word as well as any additional words. This option applies to all of the primary options above EXCEPT STOPWORDS. This option is true by default for all of the options EXCEPT SPELLCHECK.

EXCLUSIVE
Search only for the additional words, and NOT for the original word. This option applies to all of the primary options above EXCEPT STOPWORDS. This options is true by default for SPELLCHECK.

PARTOFSPEECH=pos
Limit searches to the named part of speech when searching the thesaurus or dictionary. pos can be NOUN, VERB, ADJECTIVE, ADVERB.

SENSE=n
Limit searches to the specified sense when searching the thesaurus or dictionary. Senses can be seen by looking up the definition with the $LOOKUP function or ODXSQL's LOOKUP command.

MAX_SENSES=n | ALL
Limit searches to the specified number of senses when searching the thesaurus or dictionary. By default, searches are limited to the most common sense.

Examples

The following example performs a proximity search. Proximity searches require that the ;PX option be installed on the column.

SELECT * FROM CATALOG WHERE
$contains(CONTENT, 'cell BEFORE(3) phone')

The next example searches for companies using synonyms of 'AMA', which are listed in the ALL_ACRONYMS managed synonyms list.

SELECT COMPANY FROM PROSPECTS WHERE
$CONTAINS(PROSPECTS.COMPANY, 'AMA', 'SYNONYMS=ALL_ACRONYMS', 'LBL1')

The following two criteria predicates are identical and can be used interchangeably, showing how a $CONTAINS criteria predicate can be used in place of a normal criteria predicate on any column indexed with MDK indexes.

... COMPANY = 'SYSTEMS'

... $CONTAINS(COMPANY,'SYSTEMS')

The next examples show how to use QUALIFY syntax in place of normal SELECT syntax, in a $CONTAINS predicate.

SELECT QUANTITY, STATUS FROM ORDERS WHERE $CONTAINS(QUANTITY,'(> 10)')

SELECT COMPANY FROM PROSPECTS WHERE $CONTAINS(PROSPECTS.COMPANY,'(SYNONYMS(AMA,"ALL_ACRONYMS"))',,'LBL1')

SELECT COMPANY FROM PROSPECTS WHERE $CONTAINS(PROSPECTS.COMPANY,'(systems or software)',,'LBL1')

Top