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:

Datatype Class Description
CHARACTER(n) STRING Space-filled character string of n characters
CHAR(n) STRING Synonym for CHARACTER(n)
STRING(n) STRING Null-terminated character string of n characters
VARCHAR(n)* STRING String of max n characters, with length variable
CLOB(n)* STRING Character large object of max n characters, with length variable
[SIGNED] TINYINT NUMERIC 1-byte, signed integer ( -128 to 127 )
UNSIGNED TINYINT NUMERIC 1-byte, unsigned integer ( 0 to 255 )
[SIGNED] SMALLINT NUMERIC 2-byte, signed integer ( -32768 to 32767 )
UNSIGNED SMALLINT NUMERIC 2-byte, unsigned integer ( 0 to 65535 )
[SIGNED] INTEGER NUMERIC 4-byte, signed integer ( 2147483648 to 2147483647 )
UNSIGNED INTEGER NUMERIC 4-byte, unsigned integer ( 0 to 4294967295 )
[SIGNED] BIGINT NUMERIC 8-byte, signed integer ( -2^63 to 2^63-1 )
UNSIGNED BIGINT NUMERIC 8-byte, unsigned integer ( 0 to 2^64-1 )
FLOAT NUMERIC IEEE four-byte, single-precision floating point
DOUBLE NUMERIC IEEE eight-byte, double-precision floating point
ANSI DATE DATE 10-byte ANSI date
ANSI TIME DATE 11-byte ANSI time
ANSI DATETIME DATE 24-byte ANSI datetime
ASCII DATE DATE 8-byte ASCII date ( YYYYMMDD )
ASCII DATE(6) DATE 6-byte ASCII date ( YYMMDD )
C DATETIME DATE 8-byte integer based on the C time() routine
ODBC DATE DATE 6-byte ODBC proprietary date
ODBC TIME DATE 6-byte ODBC proprietary time
ODBC DATETIME DATE 6-byte ODBC proprietary datetime
ORACLE DATE DATE 7-byte Oracle proprietary date
ORACLE TIME DATE 7-byte Oracle proprietary time
ORACLE DATETIME DATE 7-byte Oracle proprietary datetime
OMNIDEX DATE(n) DATE Omnidex proprietary date supporting n digits of YYYYMMDD
OMNIDEX TIME(n) DATE Omnidex proprietary time supporting n digits of HHMMSSNN
OMNIDEX DATETIME(n)DATE Omnidex proprietary datetime supporting n digits of YYYYMMDDHHMMSSNN
* Omnidex recommends use of the CHARACTER and STRING datatypes rather than VARCHAR and CLOB due to the requirement of a length variable.

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.

Datatype Class Description
NATIONAL CHARACTER(n) STRING Space-filled character string of n characters
NATIONAL CHAR(n) STRING Synonym for CHARACTER(n)
NATIONAL STRING(n) STRING Null-terminated character string of n characters
NATIONAL VARCHAR(n)* STRING String of max n characters, with length variable
NATIONAL CLOB(n)* STRING Character large object of max n characters, with length variable
* Omnidex recommends use of the NATIONAL CHARACTER and NATIONAL STRING datatypes rather than NATIONAL VARCHAR and NATIONAL CLOB due to the requirement of a length variable.
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

> select $convert(individual as integer) ind from individuals;

IND
-----------
  900011022
  900047761
  900047762
  900067115
  900067114
  900215567
  900240828

Example 2: $CONVERT with options

> 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

Additional Resources

See also:

 
Back to top
dev/sql/functions/convert.txt ยท Last modified: 2016/06/28 22:38 (external edit)