This is an old revision of the document!
DRAFT
Omnidex SQL: Functions
Standard SQL Functions
The Omnidex SQL Language supports several standard SQL Functions. These functions operate as defined in the SQL specification.
| SQL Functions | Description |
|---|---|
| CASE | Allows simple IF..THEN..ELSE logic in a select-item. |
| CAST | Convert an expression to a specific data type. |
| CHAR[ACTER]_LENGTH | Return the number of characters in a string. |
| || (Concatenation) | Concatenate multiple columns or 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. |
| 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. |
Omnidex Extended Functions
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 | Calculates a new date value based on an offset of days or other time vlaues |
| $COL[UMN]_LENGTH | Return the length of a column as defined in the environment catalog. Same as $COL_LEN. |
| $CONTAINS | Provides additional keyword searching options over the standard Omnidex keyword search. |
| $CONTEXT | Return snippets of text from data qualified in a $CONTAINS function. |
| $CONVERT | Convert a scalar expression from one data type to another. |
| $COMPARE_DATES | Calculates the difference between two dates in days unless a date unit is specified. |
| $CURRENT_ROW | Return the current row number. |
| $DISTANCE | Calculates the distance between two geographical points. |
| $EXTERNAL | Execute an external user-defined function. |
| $HIGHLIGHT_CRITERIA | Retrieve a select item that was used in the WHERE clause of a SELECT statement and highlights all keywords involved in the criteria |
| $HIGHLIGHT_KEYWORDS | Retrieve a select item and highlight specific 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. |
| $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. |
MIN_SCORE and FREQ_ADJUSTMENT 