Show pagesource

Old revisions

QuickLinks	Statements	Functions	OdxSQL

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