CAST specification

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram>>-CAST--(--+-expression-------+--AS--data-type--)-------------><
            +-NULL-------------+
            '-parameter-marker-'
 
data-type:
 
|--+-built-in-type-+--------------------------------------------|
   '-distinct-type-'
 
Read syntax diagramSkip visual syntax diagrambuilt-in-type:
 
|--+-+---SMALLINT---+-----------------------------------------------------------------+--|
   | +-+-INTEGER-+--+                                                                 |
   | | '-INT-----'  |                                                                 |
   | '---BIGINT-----'                                                                 |
   |                  .-(5,0)------------------------.                                |
   +-+-+-DECIMAL-+-+--+------------------------------+--------------------------------+
   | | '-DEC-----' |  |             .-,0--------.    |                                |
   | '-NUMERIC-----'  '-(--integer--+-----------+--)-'                                |
   |                                '-, integer-'                                     |
   |          .-(--53--)------.                                                       |
   +-+-FLOAT--+---------------+-+-----------------------------------------------------+
   | |        '-(--integer--)-' |                                                     |
   | +-REAL---------------------+                                                     |
   | |         .-PRECISION-.    |                                                     |
   | '-DOUBLE--+-----------+----'                                                     |
   |                    .-(--1--)-------.                                             |
   +-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+------------+-+
   | | | '-CHAR------'  '-(--integer--)-'          |  +-FOR BIT DATA---+            | |
   | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)-'  +-FOR SBCS DATA--+            | |
   | |   | '-CHAR------'          |                   +-FOR MIXED DATA-+            | |
   | |   '-VARCHAR----------------'                   '-ccsid-clause---'            | |
   | |                                 .-(--1M--)-------------.                     | |
   | '---+-CLOB-------------------+----+----------------------+--+----------------+-' |
   |     +-CHAR LARGE OBJECT------+    '-(--integer--+---+--)-'  +-FOR SBCS DATA--+   |
   |     '-CHARACTER LARGE OBJECT-'                  +-K-+       +-FOR MIXED DATA-+   |
   |                                                 +-M-+       '-ccsid-clause---'   |
   |                                                 '-G-'                            |
   |                .-(--1--)-------.                                                 |
   +-+---GRAPHIC----+---------------+-------+--+--------------+-----------------------+
   | |              '-(--integer--)-'       |  '-ccsid-clause-'                       |
   | +-+-GRAPHIC VARYING-+--(--integer--)---+                                         |
   | | '-VARGRAPHIC------'                  |                                         |
   | |             .-(--1M--)-------------. |                                         |
   | '---DBCLOB----+----------------------+-'                                         |
   |               '-(--integer--+---+--)-'                                           |
   |                             +-K-+                                                |
   |                             +-M-+                                                |
   |                             '-G-'                                                |
   |             .-(--1--)-------.                                                    |
   +-+-+-BINARY--+---------------+---------+-----------------+------------------------+
   | | |         '-(--integer--)-'         |                 |                        |
   | | '-+-BINARY VARYING-+--(--integer--)-'                 |                        |
   | |   '-VARBINARY------'                                  |                        |
   | |                              .-(--1M--)-------------. |                        |
   | '---+-BLOB----------------+----+----------------------+-'                        |
   |     '-BINARY LARGE OBJECT-'    '-(--integer--+---+--)-'                          |
   |                                              +-K-+                               |
   |                                              +-M-+                               |
   |                                              '-G-'                               |
   +-+-DATE-------------------+-------------------------------------------------------+
   | |       .-(--0--)-.      |                                                       |
   | +-TIME--+---------+------+                                                       |
   | |            .-(--6--)-. |                                                       |
   | '-TIMESTAMP--+---------+-'                                                       |
   |               .-(--200--)-----.                                                  |
   +-----DATALINK--+---------------+--+--------------+--------------------------------+
   |               '-(--integer--)-'  '-ccsid-clause-'                                |
   '---ROWID--------------------------------------------------------------------------'
 
ccsid-clause:
 
                   .-NOT NORMALIZED-.
|--CCSID--integer--+----------------+---------------------------|
                   '-NORMALIZED-----'
 

The CAST specification returns the cast operand (the first operand) cast to the type specified by the data-type. If the data type of either operand is a distinct type, the privileges held by the authorization ID of the statement must include USAGE authority on the distinct type.

expression
Specifies that the cast operand is an expression other than NULL or a parameter marker. The result is the argument value converted to the specified target data type.

The supported casts are shown in Table 13, where the first column represents the data type of the cast operand (source data type) and the data types across the top represent the target data type of the CAST specification. If the cast is not supported, an error is returned.

When casting character or graphic strings to a character or graphic string with a different length, a warning is returned if truncation of other than trailing blanks occurs.

NULL
Specifies that the cast operand is the null value. The result is a null value that has the specified data-type.
parameter-marker
A parameter marker (specified as a question mark character) is normally considered an expression, but is documented separately in this case because it has a special meaning. If the cast operand is a parameter-marker, the specified data-type is considered a promise that the replacement will be assignable to the specified data-type (using storage assignment rules, see Assignments and comparisons). Such a parameter marker is called a typed parameter marker. Typed parameter markers will be treated like any other typed value for the purpose of DESCRIBE of a select list or for column assignment.
data-type
Specifies the data type of the result. If the data type is not qualified, the SQL path is used to find the appropriate data type. For more information, see Unqualified function, procedure, specific, and distinct type names. For a description of data-type, see CREATE TABLE. (For portability across operating systems, when specifying a floating-point data type, use REAL or DOUBLE instead of FLOAT.)

Restrictions on the supported data types are based on the specified cast operand.

If the CCSID attribute is not specified, then:

If the CCSID attribute is specified, the data will converted to that CCSID. If NORMALIZED is specified, the data will be normalized.

For information on which casts between data types are supported and the rules for casting to a data type see Casting between data types.

Examples