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. |
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[UMN]_LENGTH | Return the length of a column as defined in the environment catalog. Same as $COL_LEN. |
$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 | |
$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. |
$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. |