This shows you the differences between two versions of the page.
|
dev:sql:functions:convert [2010/07/28 18:03] els |
dev:sql:functions:convert [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | {{page>:sql_bar&nofooter&noeditbtn}} | ||
| - | |||
| - | ====== Omnidex SQL Function: $CONVERT ====== | ||
| - | ===== Description ===== | ||
| - | The CONVERT function converts an expression to the requested datatype. This is similar to the CAST function, but allows a wider variety of options. | ||
| - | |||
| - | This function returns the datatype requested in the function. | ||
| - | |||
| - | ===== Syntax ===== | ||
| - | |||
| - | $CONVERT(expression, datatype [, length [, options ]] | ||
| - | |||
| - | == expression == | ||
| - | |||
| - | Required. An expression using SQL language that produces a value. A scalar expression is generally equivalent to a select item in the SQL SELECT statement, and can be a character expression, numeric expression or date expression. | ||
| - | |||
| - | == datatype == | ||
| - | Required. One of the datatypes listed below: | ||
| - | |||
| - | {{page>dev:appendix:reference:datatypes_insert&noheader&nofooter&noeditbtn}} | ||
| - | |||
| - | Omnidex also provides partial support for National Character Datatypes (sometimes called Unicode datatypes or wide characters). The Omnidex SQL Engine will process these datatypes, but the Omnidex Indexing Engine will only index the lower 8 bits of these datatypes. | ||
| - | |||
| - | {{page>dev:appendix:reference:natl_datatypes_insert&noheader&nofooter&noeditbtn}} | ||
| - | |||
| - | == length == | ||
| - | Optional. The length of the datatype is usually specified in the //datatype// parameter; however, if it has not been specified, it may be specified here. If the length is specified in the //datatype// parameter, this parameter is left empty. | ||
| - | |||
| - | == options == | ||
| - | |||
| - | Optional. Options can be applied to the data conversion to affect the output. The supported options are: | ||
| - | |||
| - | ^Option ^Description ^ | ||
| - | |LJ |Left-justify | | ||
| - | |RJ |Right-justify | | ||
| - | |UPS |Upshift | | ||
| - | |DNS |Downshift | | ||
| - | |SF |Space-fill | | ||
| - | |ZF |Zero-fill | | ||
| - | |BWZ |Blank when zero | | ||
| - | |||
| - | |||
| - | ===== Example ===== | ||
| - | ==== Example 1: $CONVERT of column ==== | ||
| - | |||
| - | <code> | ||
| - | > select $convert(individual as integer) ind from individuals; | ||
| - | |||
| - | IND | ||
| - | ----------- | ||
| - | 900011022 | ||
| - | 900047761 | ||
| - | 900047762 | ||
| - | 900067115 | ||
| - | 900067114 | ||
| - | 900215567 | ||
| - | 900240828 | ||
| - | </code> | ||
| - | |||
| - | ==== Example 2: $CONVERT with options ==== | ||
| - | |||
| - | <code> | ||
| - | > select $convert(name, 'char(50)',,'rj dns') name_rj from individuals; | ||
| - | |||
| - | NAME_RJ | ||
| - | -------------------------------------------------- | ||
| - | ms. mary lewis | ||
| - | mrs. alice r tolliver | ||
| - | mr. sidney k tolliver | ||
| - | mrs. jennifer j hopkins | ||
| - | mr. charlie hopkins | ||
| - | ms. kimberly blue | ||
| - | kathleen tucker | ||
| - | </code> | ||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | See also: | ||
| - | {{page>dev:sql:functions:see_also_utility&noheader&nofooter&noeditbtn}} | ||
| - | |||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||
| - | |||