This is an old revision of the document!
DRAFT
Omnidex SQL: Functions
Standard ANSI SQL Functions
The Omnidex SQL Language supports several standard SQL Functions based on the ANSI SQL standard as well as functions typically found in the major relational databases.
| SQL Functions | Syntax | Description |
|---|---|---|
| ABS | ABS(numeric_expression) | Returns the absolute value of a numeric expression. |
| CASE | xxx | Allows simple IF..THEN..ELSE logic in a select-item. |
| CAST | xxx | Convert an expression to a specific data type. |
| CHAR[ACTER]_LENGTH | xxx | Return the number of characters in a string. |
| COALESCE | COALESCE(value[, value…]) | Returns the first non-NULL expression in the list. |
| || (Concatenation) | xxx | Concatenate multiple columns or strings into a single character string. |
| CURRENT_DATE | xxx | Return the current data from the system clock. |
| CURRENT_TIME | xxx | Return the current time from the system clock. |
| CURRENT_TIMESTAMP | xxx | Return the current timestamp from the system clock. |
| CURRENT_USER | xxx | Return the current Omnidex user. |
| EXTRACT | xxx | Extract an individual field value from a date_class data type field. |
| LOWER | xxx | Convert all characters in a text string to lower case. |
| MOD | MOD(number1, number2) | Returns the remainder of one number divided into another. |
| POSITION | xxx | Return the position of a string within another string. |
| SUBSTRING | xxx | Return a specific part of a string. |
| SESSION_USER | xxx | Return the current Omnidex user. |
| SYSTEM_USER | xxx | Return the current system user. |
| TRIM | xxx | Remove leading and/or trailing “pad” characters from a string. |
| UPPER | xxx | Convert all characters in a text string to upper case. |
| USER | xxx | 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 
Oracle Functions
The following functions can be used within an Omnidex SQL statement after the SET SQL_SYNTAX ORACLE statement has been issued or declared in the CREATE ENVIRONMENT statement.
| Syntax | Description | |
|---|---|---|
| CHR(integer-expression) | Returns the Ascii character for the specified integer value | |
| INSTR(string, substring[, position[, occurrence\]\] ) | Returns an integer value representing the starting position of a string within the search string. | |
| NEW_TIME | NEW_TIME(datetime, from-timezone, to-timezone) | Returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date. |
| NVL | NVL(expr1, expr2) | Returns another value if the primary value is NULL. |