This shows you the differences between two versions of the page.
dev:sql:functions:home [2010/06/11 23:11] tdo |
dev:sql:functions:home [2016/06/28 22:38] |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | {{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. | ||
- | |||
- | ^ 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. | | ||
- | | [[ dev:sql:functions:character_length:home | 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. | | ||
- | | [[ dev:sql:functions:concatenation | || (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| | ||
- | | [[ 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:contains:home | $CONTAINS ]] | Provides additional keyword searching options over the standard Omnidex keyword search.| | ||
- | | [[ dev:sql:functions:context:home | $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. | | ||
- | | [[ [[ 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:highlight_keywords:home | $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. | | ||
- | | [[ [[ dev:sql:functions:lookup:home | $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 FIXME | ||
- | ===== 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 | 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. | | ||
- | |||
- | |||
- | |||
- | |||
- | |||
- | {{page>:bottom_add&nofooter&noeditbtn}} |