Functions

SQL2 Functions | Omnidex Extended Functions

Omnidex supports most SQL2 standard functions as well as several extended functions. Extended functions are prefixed with a $ character.

SQL Functions

CASE
Allows simple IF..THEN..ELSE logic in a select-item.

CAST
Convert an expression to a specific data type.

CHAR_LENGTH | 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.

Extended Functions

$COL_LEN | $COLUMN_LENGTH
Return the length of a column as defined in the environment catalog.

$CONTEXT
Return snippets of text from data qualified in a $CONTAINS function.

$CONVERT
Convert a scalar expression from one data type to another.

$CURRENT_ROW
Return the current row number.

$EXTERNAL
Execute an external user-defined function.

$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.

Top