This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
dev:sql:functions:overview [2009/11/30 19:15] tdo |
— (current) | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
| - | ====== Omnidex SQL: Functions ====== | ||
| - | <code> | ||
| - | * $contains | ||
| - | [[ $CONTEXT[(length[, 'options'[, 'label'] ]) ] | ||
| - | [ $score ]] | ||
| - | [ $HIGHLIGHT_CRITERIA ] | ||
| - | [ $HIGHLIGHT_KEYWORDS ] | ||
| - | * $lookup | ||
| - | * $distance | ||
| - | * $SOUNDEX | ||
| - | * $METAPHONE | ||
| - | * $DOUBLE_METAPHONE | ||
| - | EXTRACT(field FROM datetime_expression) | ||
| - | $CALC_DATE | ||
| - | $COMPARE_DATES | ||
| - | |||
| - | * $uniquekey - column list | ||
| - | * $omnidex - special Omnidex table with one row and one column | ||
| - | </code> | ||
| - | |||
| - | ===== Standard SQL Functions ===== | ||
| - | |||
| - | ^ 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 %%|%% 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. | | ||
| - | |||
| - | ===== Omnidex 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. | ||
| - | |||
| - | |||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||