4.9.2.9 CAST function (SQL-92 compatible)

Syntax

CAST ( { expression | NULL } AS data_type [(length)] )

Description

The scalar function CAST converts an expression to another data type. The first argument is the expression to be converted. The second argument is the target data type.

The length option for the data_type argument specifies the length for conversions to CHAR and VARCHAR data types. If omitted, the default is 30 bytes.

If the expression evaluates to null, the result of the function is null. Specifying NULL with the CAST function is useful for set operations such as UNION that require two table to have the same structure. CAST NULL allows you to specify a column of the correct data type so a table with a similar structure to another, but with fewer columns, can be in a union operation with the other table.

The CAST function provides a data-type-conversion mechanism compatible with the SQL-92 standard.

Use the CONVERT function, enclosed in the ODBC escape clause {fn }, to specify ODBC-compliant syntax for data type conversion. See the CONVERT (ODBC compatible) function for more information.

Example

The following SQL example uses CAST to convert an integer field from a catalog table to a character data type:

SELECT CAST(fld AS CHAR(25)), fld  FROM systpe.syscalctable;

CONVERT(CHARACTER(25),FLD)          FLD
--------------------------          ---
100                                 100
1 record selected