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/08 21:07] els |
dev:sql:functions:home [2012/10/26 14:57] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
{{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
- | ====== Omnidex SQL: Functions ====== | + | |
+ | Omnidex Supports standard, extended, and RDBMS specific functions as follows: | ||
+ | |||
+ | * [[dev:sql:functions:standard:home | Standard SQL Functions ]] | ||
+ | * [[dev:sql:functions:extended:home | Extended SQL Functions ]] | ||
+ | * [[dev:sql:functions:oracle:home | Oracle SQL Functions ]] | ||
+ | * [[dev:sql:functions:string:home | String Functions ]] | ||
+ | * [[dev:sql:functions:math:home | Math Functions ]] | ||
+ | * [[dev:sql:functions:text:home | Extended Text Functions ]] | ||
+ | ====== Omnidex SQL Functions Quick Reference ====== | ||
+ | |||
+ | /* (This will be converted to a three-column once the underlying pages are written) */ | ||
^ Omnidex SQL Functions ^^^ | ^ Omnidex SQL Functions ^^^ | ||
|**String Functions** ||| | |**String Functions** ||| | ||
Line 33: | Line 45: | ||
|[[dev:sql:functions:case|CASE]] ||| | |[[dev:sql:functions:case|CASE]] ||| | ||
|[[dev:sql:functions:coalesce|COALESCE]] ||| | |[[dev:sql:functions:coalesce|COALESCE]] ||| | ||
- | |[[dev:sql:functions:ifnull|IFNULL]] ||| | ||
|[[dev:sql:functions:nullif|NULLIF]] ||| | |[[dev:sql:functions:nullif|NULLIF]] ||| | ||
+ | |[[dev:sql:functions:ifnull|$IFNULL]] ||| | ||
| ||| | | ||| | ||
|**Utility and Conversion Functions** ||| | |**Utility and Conversion Functions** ||| | ||
Line 42: | Line 54: | ||
|[[dev:sql:functions:data_length|$DATA_LENGTH]] ||| | |[[dev:sql:functions:data_length|$DATA_LENGTH]] ||| | ||
|[[dev:sql:functions:distance|$DISTANCE]] ||| | |[[dev:sql:functions:distance|$DISTANCE]] ||| | ||
- | |[[dev:sql:functions:external|$EXTERNAL]] ||| | ||
| ||| | | ||| | ||
|**Variables** ||| | |**Variables** ||| | ||
Line 55: | Line 66: | ||
| ||| | | ||| | ||
|**Omnidex Text Functions** ||| | |**Omnidex Text Functions** ||| | ||
- | |[[dev:sql:functions:contains|$CONTAINS]] ||| | + | |[[dev:sql:functions:contains:home|$CONTAINS]] ||| |
- | |[[dev:sql:functions:context|$CONTEXT]] ||| | + | |[[dev:sql:functions:context:home|$CONTEXT]] ||| |
- | |[[dev:sql:functions:highlight_criteria|$HIGHLIGHT_CRITERIA]] ||| | + | |[[dev:sql:functions:highlight_criteria:home|$HIGHLIGHT_CRITERIA]] ||| |
- | |[[dev:sql:functions:highlight_keywords|$HIGHLIGHT_KEYWORDS]] ||| | + | |[[dev:sql:functions:highlight_keywords:home|$HIGHLIGHT_KEYWORDS]] ||| |
- | |[[dev:sql:functions:lookup|$LOOKUP]] ||| | + | |[[dev:sql:functions:lookup:home|$LOOKUP]] ||| |
|[[dev:sql:functions:retrieve_file|$RETRIEVE_FILE]] ||| | |[[dev:sql:functions:retrieve_file|$RETRIEVE_FILE]] ||| | ||
|[[dev:sql:functions:score|$SCORE]] ||| | |[[dev:sql:functions:score|$SCORE]] ||| | ||
Line 70: | Line 81: | ||
|[[dev:sql:functions:nvl|NVL]] ||| | |[[dev:sql:functions:nvl|NVL]] ||| | ||
|[[dev:sql:functions:power|POWER]] ||| | |[[dev:sql:functions:power|POWER]] ||| | ||
- | |[[dev:sql:functions:substr|SUBSTR]] ||| | + | |[[dev:sql:orafunctions:substr|SUBSTR]] ||| |
- | |[[dev:sql:functions:sysdate|SYSDATE]] ||| | + | |[[dev:sql:orafunctions:sysdate|SYSDATE]] ||| |
- | |[[dev:sql:functions:systimestamp|SYSTIMESTAMP]] ||| | + | |[[dev:sql:orafunctions:systimestamp|SYSTIMESTAMP]] ||| |
- | |[[dev:sql:functions:sys_extract_utc|SYS_EXTRACT_UTC]] ||| | + | |[[dev:sql:orafunctions:sys_extract_utc|SYS_EXTRACT_UTC]] ||| |
- | |[[dev:sql:functions:to_date|TO_DATE]] ||| | + | |[[dev:sql:orafunctions:to_date|TO_DATE]] ||| |
- | |[[dev:sql:functions:to_number|TO_NUMBER]] ||| | + | |[[dev:sql:orafunctions:to_number|TO_NUMBER]] ||| |
| ||| | | ||| | ||
|----------------------------------------------------------|---------------------------------------------------------|----------------------------------------------------------| | |----------------------------------------------------------|---------------------------------------------------------|----------------------------------------------------------| | ||
- | ===== String Functions ===== | + | ====== Omnidex SQL Function Descriptions ====== |
- | + | ||
- | ^Function ^Syntax ^Description ^ | + | |
- | | [[dev:sql:functions:character_length | CHAR[ACTER]_LENGTH]] |char_length(char_expr) | Return the number of characters in a character expression. | | + | |
- | | [[dev:sql:functions:lower | LOWER]] | LOWER(char_expr)|Convert all characters in a character expression to lower case. | | + | |
- | | [[dev:sql:functions:position | POSITION]] | POSITION(search_expr IN target_expr)| Return the position of a character expression within another character expression. | | + | |
- | | [[dev:sql:functions:substring | 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 ]] | TRIM(['char' FROM] column)| Remove leading and/or trailing spaces or characters from a character expression. | | + | |
- | | [[dev:sql:functions:upper | 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 ]] | $LJ(char_expr) | Left justify a character expression eliminating leading white space. | | + | |
- | | [[dev:sql:functions:lpad | $LPAD ]] | $LPAD(char_expr, len [, pad_char ])| Add leading 'pad' characters to a character expression. | | + | |
- | | [[dev:sql:functions:proper | $PROPER ]] | | 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 ]] | $RJ(char_expr) | Right justify a character expression eliminating trailing white space. | | + | |
- | | [[dev:sql:functions:rpad | $RPAD ]] | $RPAD(char_expr, len [, pad_char ])| Add trailing 'pad' characters to a character expression. | | + | |
- | ^Function ^Description ^ | + | ^Function ^Parameters ^Description ^ |
- | | [[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. | | + | ^**String Functions** ^^^ |
- | | [[dev:sql:functions:position | POSITION(search_expr IN target_expr) ]] | Return the position of a character expression within another character expression. | | + | | [[dev:sql:functions:character_length | CHAR[ACTER]_LENGTH]] |(char_expr) | Return the number of characters in a 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:lower | LOWER]] | (char_expr)|Convert all characters in a character expression to lower case. | |
- | | [[dev:sql:functions:trim | TRIM(char_expr) ]] | Remove leading and/or trailing spaces or characters from a character expression. | | + | | [[dev:sql:functions:position | POSITION]] | (search_expr IN target_expr)| Return the position of a character expression within another character expression. | |
- | | [[dev:sql:functions:upper | UPPER(char_expr)]] | Convert all characters in a character expression to upper case. | | + | | [[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:concatenation | char_expr %%||%% char_expr ]] | Concatenate multiple character expressions into a single character expression. | | + | | [[dev:sql:functions:trim | TRIM ]] | (['char' FROM] column)| Remove leading and/or trailing spaces or characters from a character expression. | |
- | | [[dev:sql:functions:lj | $LJ(char_expr) ]] | Left justify a character expression eliminating leading white space. | | + | | [[dev:sql:functions:upper | UPPER ]] | (column)| Convert all characters in a character expression to upper case. | |
- | | [[dev:sql:functions:lpad | $LPAD(char_expr, len [, pad_char ]) ]]| Add leading 'pad' characters to a character expression. | | + | | [[dev:sql:functions:concatenation | || (Concatenation) ]] | char_expr %%||%% char_expr | Concatenate multiple character expressions into a single 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:lj | $LJ ]] | (char_expr) | Left justify a character expression eliminating leading white space. | |
- | | [[dev:sql:functions:rj | $RJ(char_expr) ]] | Right justify a character expression eliminating trailing white space. | | + | | [[dev:sql:functions:lpad | $LPAD ]] | (char_expr, len [, pad_char ])| Add leading 'pad' characters to a character expression. | |
- | | [[dev:sql:functions:rpad | $RPAD(char_expr, len [, pad_char ])| Add trailing '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}} |