Show page
Old revisions

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision 		Previous revision
dev:sql:functions:contains:home [2009/11/16 22:25]
127.0.0.1 external edit 		dev:sql:functions:contains:home [2016/06/28 22:38] (current)
Line 1: Line 1:
-====== $CONTAINS ======+{{page>:​top_add&​nofooter&​noeditbtn}}
  
-===== Syntax ​=====+====== Development:​ Omnidex SQL ======
  
-  ​$CONTAINS(column, criteria, [, options [,label]])+===== SQL Functions: ​$CONTAINS ​======
  
-column The column against which the criteria should be applied.+**[[dev:​sql:​functions:​contains:​home|Description]]** |  
 +[[dev:​sql:​functions:​contains:​syntax|Syntax]] | 
 +[[dev:​sql:​functions:​contains:​customization|Customization]] | 
 +[[dev:​sql:​functions:​contains:​examples|Examples]] ​
  
-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.+==== Description ====
  
-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:+The $CONTAINS function expands ​the search capabilities of SQL to include more flexible textual searches.  ​
  
-PROXIMITY[=approach] Convert all unquoted spaces ​in criteria ​to proximity ​searches ​based on approach.  ​Valid approaches ​are:  +Most SQL statements are written with criteria ​in simple predicates, such as: FIRST_NAME = ‘William’. ​ This predicate compares the entire contents of FIRST_NAME against the criteria ​‘William’,​ providing no opportunities for more flexible ​searches.  ​If the criterion does not match the column exactly, then the row will not be included. ​ Given the likelihood of everything from nicknames to misspellings,​ this means that a lot of rows are “lost”, never to be seen by the user despite their importance and relevance. ​  
- 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).+SQL does provide ​a LIKE operator that allows exact substring searches ​and pattern-match searches.  ​But this is very limited, and does not accommodate nicknames and misspellings. ​ Furthermore, the LIKE operator has no awareness of the parsed words in a field.  ​LIKE’s substring searches would find the word “LACE” in “WALLACE”, and the word “OLD” in “REYNOLDS” despite their lack of relevance.
  
-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.+There is no standardized SQL to support textual searches involving keyword searches, proximity searches, synonym searches, spellchecking and similar features.  ​Some database vendors have implemented their own independent approaches, but they vary between databases.
  
-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.+This function returns a BOOLEAN datatype and can only be referenced ​in the WHERE clause of a SELECT statement.
  
-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. +
- +
-===== Examples ===== +
-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’)+
  
 +**[[dev:​sql:​functions:​contains:​syntax|Next]]**
  
 +====== Additional Resources ======
  
 +See also:
 +{{page>​dev:​sql:​functions:​see_also_string&​nofooter&​noeditbtn&​noheader}}
  
 +{{page>:​bottom_add&​nofooter&​noeditbtn}}
 
Back to top
dev/sql/functions/contains/home.1258410354.txt.gz · Last modified: 2016/06/28 22:38 (external edit)
Sitemap Recent Changes