This is an old revision of the document!
| SQL Functions | Description |
|---|---|
| CASE | Allows simple IF..THEN..ELSE logic in a select-item. |
| CAST | Convert an expression to a specific data type. |
| CHAR_LENGTH | Return the number of characters in a string. |
| CHARACTER_LENGTH | Return the number of characters in a string. |
| || (Concatenation) | Concatenate multiple characters strings into a single character string. |
| CURRENT_DATE | Return the current data from the system clock. |
| CURRENT_TIME | Return the current time from the system clock. |
| CURRENT_TIMESTAMP | Return the current timestamp from the system clock. |
| CURRENT_USER | Return the current Omnidex user. |
| EXTRACT | Extract an individual field value from a date_class data type field. |
| LOWER | Convert all characters in a text string to lower case. |
| POSITION | Return the position of a string within another string. |
| SUBSTRING | Return a specific part of a string. |
| SESSION_USER | Return the current Omnidex user. |
| SYNONYMS | Use synonyms of a word or phrase as qualification criteria. |
| SYSTEM_USER | Return the current system user. |
| TRIM | Remove leading and/or trailing “pad” characters from a string. |
| UPPER | Convert all characters in a text string to upper case. |
| USER | Return the current Omnidex user. |
The Omnidex SQL Language supports several functions that are extensions to the standard SQL Functions. Extended functions are prefixed with a $ so that they are easy to locate in existing SQL code.
| Omnidex Functions | Description |
|---|---|
| $CALC_DATE | |
| $COL_LEN | Return the length of a column as defined in the environment catalog. |
| $COLUMN_LENGTH | Return the length of a column as defined in the environment catalog. |
| $CONTAINS | |
| $CONTEXT | Return snippets of text from data qualified in a $CONTAINS function. |
| $CONVERT | Convert a scalar expression from one data type to another. |
| $COMPARE_DATE | |
| $CURRENT_ROW | Return the current row number. |
| $DISTANCE | |
| $DOUBLE_METAPHONE | |
| $EXTERNAL | Execute an external user-defined function. |
| $HIGHLIGHT_CRITERIA | |
| $HIGHLIGHT_KEYWORDS | |
| $IFNULL | Specify a return value for columns containing null values. |
| $LJ | Left justify a string by eliminating leading white space. |
| $LOOKUP | Retrieve textual metadata. |
| $LPAD | Add leading “PAD” characters to a string. |
| $METAPHONE | |
| $MOD | Return “n modulus y” (remainder). |
| $PROPER | Shift the first letter of each word in a string to upper case and all other letters to lower case. |
| $RANDOM | Return a pseudo-random number. |
| $RJ | Right justify a string by eliminating trailing white space and inserting leading spaces as needed. |
| $ROUND | Round a numerical value to the specified number of decimal places. |
| $RPAD | Add trailing “PAD” characters to a string. |
| $SCORE | Returns the rank/relevancy score of qualified text from a $CONTAINS function. |
| $SOUNDEX | Return the Soundex equivalent to a character string. |
| $TRUNC | Return a numeric expression truncated to a specified number of digits to the right of the decimal point. |
The $CONTEXT function is used to retrieve small excerpts from a document containing the search criteria. The $CONTEXT function returns the words adjacent to the criteria, formatting them if requested so that they keywords are easily visible. The $CONTEXT function can return multiple of these contexts in one string, allowing the user to see the various locations which led to an entry being qualified.
$CONTEXT[(length [,[‘options’] [,’label’]])]
An optional parameter that controls the length of the string to be returned. If this parameter is not provided, the default size is 256 bytes.
An optional parameter that controls options for the function. Valid options are:
Limit the number of context excerpts to n.
Render the result in the following style:
TEXT (default) HTML NONE
Render the result using the following format:
COMPRESSED (default) UNCOMPRESSED
Use HTML classes so that HTML Style Sheets can be used to determine highlighting characteristics.
When used with the CLASSES option, use different classes so that each word can have independent highlighting characteristics.
When used with the CLASSES option, use different classes so that derived words can have independent highlighting characteristics than original words. When used without CLASSES, but with STYLE=HTML, use <st> for original words and <em> for derived words.
Control whether tabs, linefeeds and other formatting characters are preserved in the output. Set on ON (default when FORMAT=UNCOMPRESSED) or OFF (default when FORMAT=COMPRESSED).
HTML tag to use to highlight keywords when STYLE=HTML. Defaults to “<strong>”.
HTML tag to use to end the highlighting of keywords when STYLE=HTML. Defaults to the HIGHLIGHT string with an initial backslash, such as “</strong>”. This option is only needed with more complex HIGHLIGHT strings.
An optional label that describes this particular search. This label is not needed unless multiple $CONTAINS functions are used in the SQL statement. This label refers to the label used in the $CONTAINS function. The label is case-insensitive, and may be up to 32 characters long.
$HIGHLIGHT_CRITERIA(column [, [length] [,[’options’] [, ‘label’]]])
The $HIGHLIGHT_CRITERIA function is used to retrieve a select item that was also used in the WHERE clause of a SELECT statement, and also highlight all keywords involved in the criteria. If Omnidex Text features such as synonyms, forms and misspellings are used, then the derived words will also be highlighted.
A column or expression to be returned and highlighted.
An optional parameter that controls the length of the string to be returned. If this parameter is not provided, the default size is the length of expression. Note that highlighting increases the space requirements, so length should likely be larger than the normal length of expression.
An optional parameter that controls options for the function. Valid options are:
Render the result in the following style:
TEXT (default) HTML NONE
Use HTML classes so that HTML Style Sheets can be used to determine highlighting characteristics.
When used with the CLASSES option, use different classes so that each word can have independent highlighting characteristics.
When used with the CLASSES option, use different classes so that derived words can have independent highlighting characteristics than original words. When used without CLASSES, but with STYLE=HTML, use <st> for original words and <em> for derived words.
HTML tag to use to highlight keywords when STYLE=HTML. Defaults to “<strong>”.
HTML tag to use to end the highlighting of keywords when STYLE=HTML. Defaults to the HIGHLIGHT string with an initial backslash, such as “</strong>”. This option is only needed with more complex HIGHLIGHT strings.
An optional label that describes this particular search. This label is needed when column is found multiple times in the criteria. This label refers to the label used in the $CONTAINS function. The label is case-insensitive, and may be up to 32 characters long.
$HIGHLIGHT_KEYWORDS (column, keywords, [, [length] [,[’options’]]])
The $HIGHLIGHT_KEYWORDS function is used to retrieve a select item and highlight specific keywords. Unlike $CONTEXT and $HIGHLIGHT_CRITERIA, the $HIGHLIGHT If Omnidex Text features such as synonyms, forms and misspellings are used, then the derived words will also be highlighted.
A column or expression to be returned and highlighted.
A string or expression containing the keywords to be highlighted.
An optional parameter that controls the length of the string to be returned. If this parameter is not provided, the default size is the length of expression. Note that highlighting increases the space requirements, so length should likely be larger than the normal length of expression.
An optional parameter that controls options for the function. Valid options are:
Render the result in the following style:
TEXT (default) HTML NONE
Use HTML classes so that HTML Style Sheets can be used to determine highlighting characteristics.
When used with the CLASSES option, use different classes so that each word can have independent highlighting characteristics.
HTML tag to use to highlight keywords when STYLE=HTML. Defaults to “<strong>”.
HTML tag to use to end the highlighting of keywords when STYLE=HTML. Defaults to the HIGHLIGHT string with an initial backslash, such as “</strong>”. This option is only needed with more complex HIGHLIGHT strings.