Omnidex SQL Function: CAST

Description

The CAST function converts an expression to the requested datatype.

This function returns the datatype requested in the function.

Syntax

CAST(expression AS datatype)
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.

Example

Example 1: CAST of column

> select cast(individual as integer) ind from individuals;

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

Additional Resources

See also:

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