This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
dev:sql:functions:home [2010/07/02 09:10] tdo |
dev:sql:functions:home [2012/10/26 14:57] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
| - | ====== Omnidex SQL: Functions ====== | ||
| - | {{page>:sql_bar&nofooter&noeditbtn}} | ||
| - | ===== 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. | + | Omnidex Supports standard, extended, and RDBMS specific functions as follows: |
| - | ^ SQL Functions ^ Syntax ^ Description ^ | + | * [[dev:sql:functions:standard:home | Standard SQL Functions ]] |
| - | | [[ ABS ]] | (column) | Returns the absolute value of a numeric expression. | | + | * [[dev:sql:functions:extended:home | Extended SQL Functions ]] |
| - | | [[ CASE ]] | WHEN x THEN x ELSE x END| Allows simple IF..THEN..ELSE logic in a select-item. | | + | * [[dev:sql:functions:oracle:home | Oracle SQL Functions ]] |
| - | | [[ CAST ]] | (column AS datatype)| Convert an expression to a specific data type. | | + | * [[dev:sql:functions:string:home | String Functions ]] |
| - | | [[ dev:sql:functions:character_length:home | CHAR[ACTER]_LENGTH ]] |char_length(column) | Return the number of characters in a string. | | + | * [[dev:sql:functions:math:home | Math Functions ]] |
| - | | [[ COALESCE ]] | (value[, value…]) | Returns the first non-NULL expression in the list. | | + | * [[dev:sql:functions:text:home | Extended Text Functions ]] |
| - | | [[ dev:sql:functions:concatenation | || (Concatenation) ]] |column !! 'string' |Concatenate multiple columns or strings into a single character string. | | + | ====== Omnidex SQL Functions Quick Reference ====== |
| - | | [[ 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]] | (dateunit FROM column)| Extract an individual field value from a date_class data type field. | | + | |
| - | | [[ LOWER]] | (column)|Convert all characters in a text string to lower case. | | + | |
| - | | [[ MOD ]] | (column, divisor) | Returns the remainder of one number divided into another. | | + | |
| - | | [[ POSITION]] |('string' in column)| Return the position of a string within another string. | | + | |
| - | | [[ SUBSTRING]] | (column FROM pos FOR len)|Return a specific part of a string. | | + | |
| - | | [[ SESSION_USER]] | | Return the current Omnidex user. | | + | |
| - | | [[ SYSTEM_USER]] | | Return the current system user. | | + | |
| - | | [[ TRIM ]] | (['char' FROM] column)| Remove leading and/or trailing spaces or characters from a string. | | + | |
| - | | [[ UPPER ]] | (column)| Convert all characters in a text string to upper case. | | + | |
| - | | [[ USER ]] | |Return the current Omnidex user. | | + | |
| - | ===== Omnidex Extended Functions ===== | + | /* (This will be converted to a three-column once the underlying pages are written) */ |
| - | 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 SQL Functions ^^^ |
| - | + | |**String Functions** ||| | |
| - | ^ Omnidex Functions ^ Description ^ | + | |[[dev:sql:functions:character_length|CHARACTER_LENGTH]] ||| |
| - | | [[ $CALC_DATE ]] | Calculates a new date value based on an offset of days or other time vlaues| | + | |[[dev:sql:functions:lower|LOWER]] ||| |
| - | | [[ dev:sql:functions:col_length | $COL[UMN]_LENGTH ]] | Return the length of a column as defined in the environment catalog. Same as $COL_LEN. | | + | |[[dev:sql:functions:position|POSITION]] ||| |
| - | | [[ dev:sql:functions:contains:home | $CONTAINS ]] | Provides additional keyword searching options over the standard Omnidex keyword search.| | + | |[[dev:sql:functions:substring|SUBSTRING]] ||| |
| - | | [[ dev:sql:functions:context:home | $CONTEXT ]] | Return snippets of text from data qualified in a $CONTAINS function. | | + | |[[dev:sql:functions:trim|TRIM]] ||| |
| - | | [[ $CONVERT ]] | Convert a scalar expression from one data type to another. | | + | |[[dev:sql:functions:upper|UPPER]] ||| |
| - | | [[ $COMPARE_DATES]] | Calculates the difference between two dates in days unless a date unit is specified.| | + | |[[dev:sql:functions:concatenation| || (Concatenation)]] ||| |
| - | | [[ $CURRENT_ROW ]] | Return the current row number. | | + | |[[dev:sql:functions:lj|$LJ]] ||| |
| - | | [[ $DISTANCE ]] | Calculates the distance between two geographical points.| | + | |[[dev:sql:functions:lpad|$LPAD]] ||| |
| - | | [[ $EXTERNAL ]] | Execute an external user-defined function. | | + | |[[dev:sql:functions:proper|$PROPER]] ||| |
| - | | [[ [[ dev:sql:functions:highlight_criteria:home | $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| | + | |[[dev:sql:functions:rj|$RJ]] ||| |
| - | | [[ [[ dev:sql:functions:highlight_keywords:home | $HIGHLIGHT_KEYWORDS ]] | Retrieve a select item and highlight specific keywords.| | + | |[[dev:sql:functions:rpad|$RPAD]] ||| |
| - | | [[ $IFNULL ]] | Specify a return value for columns containing null values. | | + | | ||| |
| - | | [[ $LJ ]] | Left justify a string by eliminating leading white space. | | + | |**Math Functions** ||| |
| - | | [[ [[ dev:sql:functions:lookup:home | $LOOKUP ]] | Retrieve textual metadata. | | + | |[[dev:sql:functions:abs|ABS]] ||| |
| - | | [[ $LPAD ]] | Add leading "PAD" characters to a string. | | + | |[[dev:sql:functions:ceiling|CEIL[ING] ]] ||| |
| - | | [[ $MOD ]] | Return "n modulus y" (remainder). | | + | |[[dev:sql:functions:floor|FLOOR]] ||| |
| - | | [[ $PROPER ]] | Shift the first letter of each word in a string to upper case and all other letters to lower case. | | + | |[[dev:sql:functions:mod|MOD]] ||| |
| - | | [[ $RANDOM ]] | Return a pseudo-random number. | | + | |[[dev:sql:functions:round|ROUND]] ||| |
| - | | [[ $RJ ]] | Right justify a string by eliminating trailing white space and inserting leading spaces as needed. | | + | |[[dev:sql:functions:trunc|TRUNC]] ||| |
| - | | [[ $ROUND ]] | Round a numerical value to the specified number of decimal places. | | + | |[[dev:sql:functions:random|$RANDOM]] ||| |
| - | | [[ $RPAD ]] | Add trailing "PAD" characters to a string. | | + | | ||| |
| - | | [[ $SCORE ]] | Returns the rank/relevancy score of qualified text from a $CONTAINS function. | | + | |**Date Functions** ||| |
| - | | [[ $SOUNDEX ]] | Return the Soundex equivalent to a character string. | | + | |[[dev:sql:functions:extract|EXTRACT]] ||| |
| - | | [[ $TRUNC ]] | Return a numeric expression truncated to a specified number of digits to the right of the decimal point. | | + | |[[dev:sql:functions:calc_date|$CALC_DATE]] ||| |
| + | |[[dev:sql:functions:compare_dates|$COMPARE_DATES]] ||| | ||
| + | | ||| | ||
| + | |**Logic Functions** ||| | ||
| + | |[[dev:sql:functions:case|CASE]] ||| | ||
| + | |[[dev:sql:functions:coalesce|COALESCE]] ||| | ||
| + | |[[dev:sql:functions:nullif|NULLIF]] ||| | ||
| + | |[[dev:sql:functions:ifnull|$IFNULL]] ||| | ||
| + | | ||| | ||
| + | |**Utility and Conversion Functions** ||| | ||
| + | |[[dev:sql:functions:cast|CAST]] ||| | ||
| + | |[[dev:sql:functions:column_length|$COLUMN_LENGTH]] ||| | ||
| + | |[[dev:sql:functions:convert|$CONVERT]] ||| | ||
| + | |[[dev:sql:functions:data_length|$DATA_LENGTH]] ||| | ||
| + | |[[dev:sql:functions:distance|$DISTANCE]] ||| | ||
| + | | ||| | ||
| + | |**Variables** ||| | ||
| + | |[[dev:sql:functions:current_date|CURRENT_DATE]] ||| | ||
| + | |[[dev:sql:functions:current_time|CURRENT_TIME]] ||| | ||
| + | |[[dev:sql:functions:current_timestamp|CURRENT_TIMESTAMP]] ||| | ||
| + | |[[dev:sql:functions:current_user|CURRENT_USER]] ||| | ||
| + | |[[dev:sql:functions:session_user|SESSION_USER]] ||| | ||
| + | |[[dev:sql:functions:system_user|SYSTEM_USER]] ||| | ||
| + | |[[dev:sql:functions:user|USER]] ||| | ||
| + | |[[dev:sql:functions:current_row| $CURRENT_ROW]] ||| | ||
| + | | ||| | ||
| + | |**Omnidex Text Functions** ||| | ||
| + | |[[dev:sql:functions:contains:home|$CONTAINS]] ||| | ||
| + | |[[dev:sql:functions:context:home|$CONTEXT]] ||| | ||
| + | |[[dev:sql:functions:highlight_criteria:home|$HIGHLIGHT_CRITERIA]] ||| | ||
| + | |[[dev:sql:functions:highlight_keywords:home|$HIGHLIGHT_KEYWORDS]] ||| | ||
| + | |[[dev:sql:functions:lookup:home|$LOOKUP]] ||| | ||
| + | |[[dev:sql:functions:retrieve_file|$RETRIEVE_FILE]] ||| | ||
| + | |[[dev:sql:functions:score|$SCORE]] ||| | ||
| + | |[[dev:sql:functions:soundex|$SOUNDEX]] ||| | ||
| + | | ||| | ||
| + | |**Oracle-specific Functions** ||| | ||
| + | |[[dev:sql:functions:chr|CHR]] ||| | ||
| + | |[[dev:sql:functions:instr|INSTR]] ||| | ||
| + | |[[dev:sql:functions:new_time|NEW_TIME]] ||| | ||
| + | |[[dev:sql:functions:nvl|NVL]] ||| | ||
| + | |[[dev:sql:functions:power|POWER]] ||| | ||
| + | |[[dev:sql:orafunctions:substr|SUBSTR]] ||| | ||
| + | |[[dev:sql:orafunctions:sysdate|SYSDATE]] ||| | ||
| + | |[[dev:sql:orafunctions:systimestamp|SYSTIMESTAMP]] ||| | ||
| + | |[[dev:sql:orafunctions:sys_extract_utc|SYS_EXTRACT_UTC]] ||| | ||
| + | |[[dev:sql:orafunctions:to_date|TO_DATE]] ||| | ||
| + | |[[dev:sql:orafunctions:to_number|TO_NUMBER]] ||| | ||
| + | | ||| | ||
| + | |----------------------------------------------------------|---------------------------------------------------------|----------------------------------------------------------| | ||
| - | MIN_SCORE and FREQ_ADJUSTMENT FIXME | + | ====== Omnidex SQL Function Descriptions ====== |
| - | ===== 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. | + | |
| - | ^ Function ^ 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 ]] | 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 ]] | (expr1, expr2) | Returns another value if the primary value is NULL. | | + | |
| + | ^Function ^Parameters ^Description ^ | ||
| + | | ||| | ||
| + | ^**String Functions** ^^^ | ||
| + | | [[dev:sql:functions:character_length | CHAR[ACTER]_LENGTH]] |(char_expr) | Return the number of characters in a character expression. | | ||
| + | | [[dev:sql:functions:lower | LOWER]] | (char_expr)|Convert all characters in a character expression to lower case. | | ||
| + | | [[dev:sql:functions:position | POSITION]] | (search_expr IN target_expr)| Return the position of a character expression within another character expression. | | ||
| + | | [[dev:sql:functions:substring | SUBSTRING]] | (char_expr FROM pos FOR len)|Return a specific part of a character expression starting at FROM position FOR length. | | ||
| + | | [[dev:sql:functions:trim | TRIM ]] | (['char' FROM] column)| Remove leading and/or trailing spaces or characters from a character expression. | | ||
| + | | [[dev:sql:functions:upper | UPPER ]] | (column)| Convert all characters in a character expression to upper case. | | ||
| + | | [[dev:sql:functions:concatenation | || (Concatenation) ]] | char_expr %%||%% char_expr | Concatenate multiple character expressions into a single character expression. | | ||
| + | | [[dev:sql:functions:lj | $LJ ]] | (char_expr) | Left justify a character expression eliminating leading white space. | | ||
| + | | [[dev:sql:functions:lpad | $LPAD ]] | (char_expr, len [, pad_char ])| Add leading 'pad' characters to a character expression. | | ||
| + | | [[dev:sql:functions:proper | $PROPER ]] | (char_expr) | Shift the first letter of each word in a character expression to upper case and all other letters to lower case. | | ||
| + | | [[dev:sql:functions:rj | $RJ ]] | (char_expr) | Right justify a character expression eliminating trailing white space. | | ||
| + | | [[dev:sql:functions:rpad | $RPAD ]] | (char_expr, len [, pad_char ])| Add trailing 'pad' characters to a character expression. | | ||
| + | | ||| | ||
| + | ^**Math Functions** ^^^ | ||
| + | | [[dev:sql:functions:abs | ABS ]] | (num_expr) | Returns the absolute value of a numeric expression. | | ||
| + | | [[dev:sql:functions:mod | MOD ]] | (int_expr, divisor) | Returns the integral remainder of one integer expression divided by another. | | ||
| + | | [[dev:sql:functions:round | ROUND ]] | (num_expr [, digits]) | Round a numerical value to the specified number of decimal places. | | ||
| + | | [[dev:sql:functions:trunc | TRUNC ]] | (num_expr [, digits]) | Return a numeric expression truncated to a specified number of digits to the right of the decimal point. | | ||
| + | | [[dev:sql:functions:random | $RANDOM ]] | (num_expr [, seed]) | Return a pseudo-random number. | | ||
| + | |--------------------------------------|-----------------------------------------------|----------------------------------------------------------------------------------------------------------| | ||
| - | + | {{page>:bottom_add&nofooter&noeditbtn}} | |
| - | + | ||
| - | + | ||
| - | {{page>:bottom_add&nofooter&noeditbtn} | + | |