$CONTAINS

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

column The column against which the criteria should be applied.

criteria Criteria to be applied against the column, consisting of either simple keywords, or an expression. Expressions may contain nested parentheses, qualification functions, Boolean operators and other textual operators described in this document. Expressions must be surrounded by outer parentheses.

options An optional parameter that controls options for the function. The primary options determine which features to apply to this search. If multiple options are submitted, they will be performed in the order shown below:

PROXIMITY[=approach] Convert all unquoted spaces in criteria to proximity searches based on approach. Valid approaches are:

	PHRASE
	BEFORE(n)
	AFTER(n)

SPELLCHECK[=list] Check each word in criteria to see if it is a commonly misspelled word, and automatically replace the word with the corrected 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).

SYNONYMS[=list] Expand the search to include the synonyms of each word in criteria. 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 thesaurus.

FORMS[=approach] Expand the search to include the various forms of each word. Approach can be set to one or more of PLURALS, CONJUGATIONS, and DERIVATIONS. 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 all approaches will be used by default.

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.

Stopwords are not eliminated if a single stopword is the only word in criteria.  

MISSPELLINGS[=list] 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.

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

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 the SYNONYMS option only, and is true by default.

EXCLUSIVE Search only for the additional words, but not for the original word. This option applies to the SYNONYMS only.

PARTOFSPEECH=pos When searching the thesaurus or dictionary, limit searches to the named part of speech. Parts of speech are:

• NOUN
• VERB
• ADJECTIVE
• ADVERB

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

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

label An optional label that describes this particular search. This label is only needed when there are multiple $CONTAINS functions in a single SELECT statement. The label is then used to associate a particular $CONTAINS function with a $SCORE or $CONTEXT function. The label is case-insensitive, and may be up to 32 characters long.

The $CONTAINS function returns a Boolean TRUE or FALSE.

This example shows how to enhance an existing SQL statement to use a $CONTAINS function:

Original Omnidex SQL Select:

select ACCT, COMPANY, CONTACT 
  from CUSTOMERS 
  where FIRST_NAME = ‘William’

Enhanced with $CONTAINS function:

select ACCT, COMPANY, CONTACT 
  from CUSTOMERS 
  where $CONTAINS(FIRST_NAME, ‘William’, 
                  ‘synonyms=all_given_names’)

This example shows how to enhance a LIKE search to use a $CONTAINS function:

select ACCT, COMPANY, CONTACT 
  from CUSTOMERS 
  where ADDRESS LIKE ‘%FIRST%STREET%’

Enhanced with $CONTAINS function:

select 	ACCT, COMPANY, CONTACT 
  from 	CUSTOMERS 
  where	$CONTAINS(ADDRESS, ‘FIRST STREET’, 
                    ‘synonyms=all_addresses’)