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 [2009/12/04 04:16] tdo |
dev:sql:functions:home [2012/10/26 14:28] (current) |
||
|---|---|---|---|
| Line 2: | Line 2: | ||
| <html><div align="center"><span style="color:red">DRAFT</span></div></html> | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
| ====== Omnidex SQL: Functions ====== | ====== Omnidex SQL: Functions ====== | ||
| + | {{page>:sql_bar&nofooter&noeditbtn}} | ||
| ===== Standard SQL Functions ===== | ===== Standard SQL Functions ===== | ||
| + | |||
| + | The Omnidex SQL Language supports several standard SQL Functions. These functions operate as defined in the SQL specification. | ||
| ^ SQL Functions ^ Description ^ | ^ SQL Functions ^ Description ^ | ||
| | [[ CASE ]] | Allows simple IF..THEN..ELSE logic in a select-item. | | | [[ CASE ]] | Allows simple IF..THEN..ELSE logic in a select-item. | | ||
| | [[ CAST ]] | Convert an expression to a specific data type. | | | [[ CAST ]] | Convert an expression to a specific data type. | | ||
| - | | [[ CHAR_LENGTH ]] | Return the number of characters in a string. | | + | | [[ dev:sql:functions:character_length:home | CHAR[ACTER]_LENGTH ]] | Return the number of characters in a string. | |
| - | | [[ CHARACTER_LENGTH ]] | Return the number of characters in a string. | | + | | [[ dev:sql:functions:concatenation | || (Concatenation) ]] | Concatenate multiple columns or strings into a single character string. | |
| - | | [[ dev:sql:functions:concatenation | || (Concatenation) ]] | Concatenate multiple characters strings into a single character string. | | + | |
| | [[ CURRENT_DATE]] | Return the current data from the system clock. | | | [[ CURRENT_DATE]] | Return the current data from the system clock. | | ||
| | [[ CURRENT_TIME]] | Return the current time from the system clock. | | | [[ CURRENT_TIME]] | Return the current time from the system clock. | | ||
| Line 19: | Line 21: | ||
| | [[ SUBSTRING]] | Return a specific part of a string. | | | [[ SUBSTRING]] | Return a specific part of a string. | | ||
| | [[ SESSION_USER]] | Return the current Omnidex user. | | | [[ 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. | | | [[ SYSTEM_USER]] | Return the current system user. | | ||
| | [[ TRIM ]] | Remove leading and/or trailing "pad" characters from a string. | | | [[ TRIM ]] | Remove leading and/or trailing "pad" characters from a string. | | ||
| | [[ UPPER ]] | Convert all characters in a text string to upper case. | | | [[ UPPER ]] | Convert all characters in a text string to upper case. | | ||
| | [[ USER ]] | Return the current Omnidex user. | | | [[ USER ]] | Return the current Omnidex user. | | ||
| + | |||
| ===== Omnidex Extended Functions ===== | ===== 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. | + | 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 ^ | ^ Omnidex Functions ^ Description ^ | ||
| | [[ $CALC_DATE ]] | | | | [[ $CALC_DATE ]] | | | ||
| - | | [[ $COL_LEN]] | Return the length of a column as defined in the environment catalog. | | + | | [[ dev:sql:functions:col_length | $COL[UMN]_LENGTH ]] | Return the length of a column as defined in the environment catalog. Same as $COL_LEN. | |
| - | | [[ $COLUMN_LENGTH ]] | Return the length of a column as defined in the environment catalog. | | + | |
| | [[ dev:sql:functions:contains:home | $CONTAINS ]] | | | | [[ dev:sql:functions:contains:home | $CONTAINS ]] | | | ||
| | [[ dev:sql:functions:context:home | $CONTEXT ]] | Return snippets of text from data qualified in a $CONTAINS function. | | | [[ dev:sql:functions:context:home | $CONTEXT ]] | Return snippets of text from data qualified in a $CONTAINS function. | | ||
| Line 38: | Line 39: | ||
| | [[ $CURRENT_ROW ]] | Return the current row number. | | | [[ $CURRENT_ROW ]] | Return the current row number. | | ||
| | [[ $DISTANCE ]] | | | | [[ $DISTANCE ]] | | | ||
| - | | [[ $DOUBLE_METAPHONE ]] | | | ||
| | [[ $EXTERNAL ]] | Execute an external user-defined function. | | | [[ $EXTERNAL ]] | Execute an external user-defined function. | | ||
| | [[ [[ dev:sql:functions:highlight_criteria:home | $HIGHLIGHT_CRITERIA ]] | | | | [[ [[ dev:sql:functions:highlight_criteria:home | $HIGHLIGHT_CRITERIA ]] | | | ||
| Line 46: | Line 46: | ||
| | [[ [[ dev:sql:functions:lookup:home | $LOOKUP ]] | Retrieve textual metadata. | | | [[ [[ dev:sql:functions:lookup:home | $LOOKUP ]] | Retrieve textual metadata. | | ||
| | [[ $LPAD ]] | Add leading "PAD" characters to a string. | | | [[ $LPAD ]] | Add leading "PAD" characters to a string. | | ||
| - | | [[ $METAPHONE ]] | | | ||
| | [[ $MOD ]] | Return "n modulus y" (remainder). | | | [[ $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. | | | [[ $PROPER ]] | Shift the first letter of each word in a string to upper case and all other letters to lower case. | | ||
| Line 56: | Line 55: | ||
| | [[ $SOUNDEX ]] | Return the Soundex equivalent to a character string. | | | [[ $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. | | | [[ $TRUNC ]] | Return a numeric expression truncated to a specified number of digits to the right of the decimal point. | | ||
| - | |||
| - | |||
| - | ===== $HIGHLIGHT_CRITERIA ===== | ||
| - | |||
| - | $HIGHLIGHT_CRITERIA(column [, [length] [,[’options’] [, ‘label’]]]) | ||
| - | |||
| - | The $HIGHLIGHT_CRITERIA function is used to retrieve a select item that was also used in the WHERE clause of a SELECT statement, and also highlight all keywords involved in the criteria. If Omnidex Text features such as synonyms, forms and misspellings are used, then the derived words will also be highlighted. | ||
| - | |||
| - | == column == | ||
| - | A column or expression to be returned and highlighted. | ||
| - | |||
| - | == length == | ||
| - | An optional parameter that controls the length of the string to be returned. If this parameter is not provided, the default size is the length of expression. Note that highlighting increases the space requirements, so length should likely be larger than the normal length of expression. | ||
| - | |||
| - | == options == | ||
| - | An optional parameter that controls options for the function. Valid options are: | ||
| - | |||
| - | == STYLE= == | ||
| - | |||
| - | Render the result in the following style: | ||
| - | TEXT (default) | ||
| - | HTML | ||
| - | NONE | ||
| - | |||
| - | == CLASSES == | ||
| - | Use HTML classes so that HTML Style Sheets can be used to determine highlighting characteristics. | ||
| - | |||
| - | == DISTINGUISH_WORDS == | ||
| - | When used with the CLASSES option, use different classes so that each word can have independent highlighting characteristics. | ||
| - | |||
| - | == DISTINGUISH_DERIVED_WORDS == | ||
| - | When used with the CLASSES option, use different classes so that derived words can have independent highlighting characteristics than original words. When used without CLASSES, but with STYLE=HTML, use <st> for original words and <em> for derived words. | ||
| - | |||
| - | == HIGHLIGHT=”string” == | ||
| - | HTML tag to use to highlight keywords when STYLE=HTML. Defaults to “<strong>”. | ||
| - | |||
| - | == END_HIGHLIGHT=”string” == | ||
| - | HTML tag to use to end the highlighting of keywords when STYLE=HTML. Defaults to the HIGHLIGHT string with an initial backslash, such as “</strong>”. This option is only needed with more complex HIGHLIGHT strings. | ||
| - | |||
| - | == label == | ||
| - | An optional label that describes this particular search. This label is needed when column is found multiple times in the criteria. This label refers to the label used in the $CONTAINS function. The label is case-insensitive, and may be up to 32 characters long. | ||
| - | |||
| - | ===== $HIGHLIGHT_KEYWORDS ===== | ||
| - | |||
| - | $HIGHLIGHT_KEYWORDS (column, keywords, [, [length] [,[’options’]]]) | ||
| - | |||
| - | The $HIGHLIGHT_KEYWORDS function is used to retrieve a select item and highlight specific keywords. Unlike $CONTEXT and $HIGHLIGHT_CRITERIA, the $HIGHLIGHT If Omnidex Text features such as synonyms, forms and misspellings are used, then the derived words will also be highlighted. | ||
| - | |||
| - | == column == | ||
| - | A column or expression to be returned and highlighted. | ||
| - | |||
| - | == keywords == | ||
| - | A string or expression containing the keywords to be highlighted. | ||
| - | |||
| - | == length == | ||
| - | An optional parameter that controls the length of the string to be returned. If this parameter is not provided, the default size is the length of expression. Note that highlighting increases the space requirements, so length should likely be larger than the normal length of expression. | ||
| - | |||
| - | == options == | ||
| - | An optional parameter that controls options for the function. Valid options are: | ||
| - | |||
| - | == STYLE= == | ||
| - | Render the result in the following style: | ||
| - | TEXT (default) | ||
| - | HTML | ||
| - | NONE | ||
| - | |||
| - | == CLASSES == | ||
| - | Use HTML classes so that HTML Style Sheets can be used to determine highlighting characteristics. | ||
| - | |||
| - | == DISTINGUISH_WORDS == | ||
| - | When used with the CLASSES option, use different classes so that each word can have independent highlighting characteristics. | ||
| - | |||
| - | == HIGHLIGHT=”string” == | ||
| - | HTML tag to use to highlight keywords when STYLE=HTML. Defaults to “<strong>”. | ||
| - | |||
| - | == END_HIGHLIGHT=”string” == | ||
| - | HTML tag to use to end the highlighting of keywords when STYLE=HTML. Defaults to the HIGHLIGHT string with an initial backslash, such as “</strong>”. This option is only needed with more complex HIGHLIGHT strings. | ||
| {{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||