CAST specification

The CAST specification returns the first operand (the cast operand) converted to the data type that is specified by data-type.


>>-CAST--(--+-expression-------+--AS--data-type--)-------------><
            +-NULL-------------+                     
            '-parameter-marker-'

data-type:


>>-+-built-in-type------+--------------------------------------><
   '-distinct-type-name-'

built-in-type:

>>-+-+-SMALLINT----+---------------------------------------------------------------------------------------------+-><
   | +-+-INTEGER-+-+                                                                                             |   
   | | '-INT-----' |                                                                                             |   
   | '-BIGINT------'                                                                                             |   
   |              .-(5,0)--------------------.                                                                   |   
   +-+-DECIMAL-+--+--------------------------+-------------------------------------------------------------------+   
   | +-DEC-----+  '-(integer-+-----------+-)-'                                                                   |   
   | '-NUMERIC-'             '-, integer-'                                                                       |   
   |           .-(34)-.                                                                                          |   
   +-DECFLOAT--+------+------------------------------------------------------------------------------------------+   
   |           '-(16)-'                                                                                          |   
   |          .-(53)------.                                                                                      |   
   +-+-FLOAT--+-----------+--+-----------------------------------------------------------------------------------+   
   | |        '-(integer)-'  |                                                                                   |   
   | +-REAL------------------+                                                                                   |   
   | |         .-PRECISION-. |                                                                                   |   
   | '-DOUBLE--+-----------+-'                                                                                   |   
   |                    .-(1 OCTETS)-.                                                                           |   
   +-+-+-+-CHARACTER-+--+------------+--------+--+-+--------------------+--+----------------------+-+----------+-+   
   | | | '-CHAR------'  '-(length)---'        |  | '-CCSID--+-ASCII---+-'  '-FOR--+-SBCS--+--DATA-' |          | |   
   | | '-+-+-CHARACTER-+--VARYING-+--(length)-'  |          +-EBCDIC--+           +-MIXED-+         |          | |   
   | |   | '-CHAR------'          |              |          '-UNICODE-'           '-BIT---'         |          | |   
   | |   '-VARCHAR----------------'              '-CCSID--integer-----------------------------------'          | |   
   | |                                  .-(1M OCTETS)--.                                                       | |   
   | '-+-+-CHARACTER-+--LARGE OBJECT-+--+--------------+--+-+--------------------+--+----------------------+-+-' |   
   |   | '-CHAR------'               |  '-(lob-length)-'  | '-CCSID--+-ASCII---+-'  '-FOR--+-SBCS--+--DATA-' |   |   
   |   '-CLOB------------------------'                    |          +-EBCDIC--+           '-MIXED-'         |   |   
   |                                                      |          '-UNICODE-'                             |   |   
   |                                                      '-CCSID--integer-----------------------------------'   |   
   |            .-(1 CODEUNITS16)-.                                                                              |   
   +-+-GRAPHIC--+-----------------+-+--+--------------------+----------------------------------------------------+   
   | |          '-(length)--------' |  '-CCSID--+-ASCII---+-'                                                    |   
   | +-VARGRAPHIC--(--length--)-----+           +-EBCDIC--+                                                      |   
   | |         .-(1M CODEUNITS16)-. |           +-UNICODE-+                                                      |   
   | '-DBCLOB--+------------------+-'           '-integer-'                                                      |   
   |           '-(lob-length)-----'                                                                              |   
   |           .-(1)-------.                                                                                     |   
   +-+-BINARY--+-----------+-------------------------+-----------------------------------------------------------+   
   | |         '-(integer)-'                         |                                                           |   
   | +-+-BINARY VARYING-+-(integer)------------------+                                                           |   
   | | '-VARBINARY------'                            |                                                           |   
   | |                          .-(1M)-------------. |                                                           |   
   | '-+-BINARY LARGE OBJECT-+--+------------------+-'                                                           |   
   |   '-BLOB----------------'  '-(integer-+---+-)-'                                                             |   
   |                                       +-K-+                                                                 |   
   |                                       +-M-+                                                                 |   
   |                                       '-G-'                                                                 |   
   +-+-DATE------------------------------------------------+-----------------------------------------------------+   
   | +-TIME------------------------------------------------+                                                     |   
   | |            .-(--6--)-------.  .-WITHOUT TIME ZONE-. |                                                     |   
   | '-TIMESTAMP--+---------------+--+-------------------+-'                                                     |   
   |              '-(--integer--)-'  '-WITH TIME ZONE----'                                                       |   
   +-ROWID-------------------------------------------------------------------------------------------------------+   
   '-XML---------------------------------------------------------------------------------------------------------'

length:


>>-integer--+-------------+------------------------------------><
            +-CODEUNITS16-+   
            +-CODEUNITS32-+   
            '-OCTETS------'

lob-length:


>>-integer--+---+--+-------------+-----------------------------><
            +-K-+  +-CODEUNITS16-+   
            +-M-+  +-CODEUNITS32-+   
            '-G-'  '-OCTETS------'

If the data type of either operand is a distinct type, the privilege set must implicitly include EXECUTE authority on the generated cast functions for the distinct type. The CAST specification allows the second operand to be cast to a particular encoding scheme or CCSID if the second operand represents character data. The CCSID clause can be specified following CHAR, VARCHAR, CLOB, GRAPHIC, VARGRAPHIC, and DBCLOB data types.

expression

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

The supported casts are shown in Casting between data types. If the cast is not supported, an error is returned.

When a character string is cast to a character string with a different length or a graphic string is cast to a graphic string with a different length, a warning occurs if any characters except trailing blanks are truncated. Start of change The warning also occurs if any characters are truncated when a BLOB operand is cast, or if the time zone characters are truncated when a TIMESTAMP WITH TIME ZONE operand is cast to a string End of change

NULL

Specifies that the cast operand is null. The result is a null value with the specified target data type.

parameter-marker

A parameter marker, which is normally considered an expression, has a special meaning as a cast operand. When the cast operand is a parameter-marker, the data type that is specified represents the "promise" that the replacement value for the parameter marker will be assignable to the specified data type (using "store assignment" rules). Such a parameter marker is considered a typed parameter marker. Typed parameter markers are treated like any other typed value for the purpose of function resolution, a DESCRIBE of a select list, or 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 SQL path. 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.)

If the cast operand is expression, see Casting between data types and use any of the target data types that are supported for the data type of the cast operand.
If the cast operand is NULL, you can use any data type.
If the cast operand is a parameter-marker:
- If the target data type is a distinct type, the application that uses the parameter marker uses the source data type of the distinct type.
- Otherwise, any data type if valid.

length

Specifies the length of the result.

You can specify that the length of the result be evaluated in a specific number of string units: CODEUNITS16, CODEUNITS32, or OCTETS. If expression is a character string that is defined as bit data, CODEUNITS16, or CODEUNITS32 cannot be specified. If expression is a graphic string, OCTETS cannot be specified.

For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see String unit specifications.

lob-length

Specifies the length of the result.

You can specify that the length of the result be evaluated in a specific number of string units: CODEUNITS16, CODEUNITS32, or OCTETS. If expression is a graphic string, OCTETS cannot be specified.

For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see String unit specifications.

CCSID encoding-scheme

Specifies the encoding scheme for the target data type. The specific CCSIDs for SBCS, BIT, and MIXED data are determined by the default CCSIDs for the server for the specified encoding scheme. The valid values are ASCII, EBCDIC, and UNICODE.

CCSID integer

Specifies that the target data type be encoded using the CCSID integer. The value must be one of the CCSID values in DECP. If the second operand is CHAR, VARCHAR, or CLOB, the CCSID specified must be either a SBCS, or MIXED CCSID, or 65535 for bit data. If the second operand is GRAPHIC, VARGRAPHIC, or DBCLOB, the CCSID specified must be a DBCS CCSID. See Determining the CCSID of the result if neither CCSID integer nor CCSID encoding-scheme is specified. See Determining the CCSID of the result for special considerations regarding CCSID 367.

Interaction between length and CCSID clauses: If both the length and CCSID clauses are specified, the data is first cast to the specified CCSID, and then the length is applied. If either CODEUNITS16 or CODEUNITS32 is specified, the specification of length applies to the units specified. That is, the data is converted to an intermediate form (in Unicode), the length is applied, and the data is converted to the specified CCSID.

Resolution of cast functions: DB2 uses the implicit or explicit schema name and the data type name of data-type, and function resolution to determine the specific function to use to convert expression to data-type. See Qualified function resolution for more information.

Result of the CAST: When numeric data is cast to character data, the data type of the result is a fixed-length character string, which is similar to the result that the CHAR function would give. (For more information, see CHAR.) When character data is cast to numeric data, the data type of the result depends on the data type of the specified number. For example, character data that is cast to an integer becomes a large integer, which is similar to the result that the INTEGER function would give. (For more information see INTEGER or INT.)

If the data type of the result is character, the subtype of the result is determined as follows:

If expression is graphic, the subtype of the result is mixed.
If expression is a datetime data type, the subtype of the result is mixed. The exception is when the default encoding scheme is EBCDIC and there is no mixed or graphic data on the system for EBCDIC.
If expression is a row ID and data-type is not CLOB, the result is bit data.
If expression is character, the subtype of the result is the same as expression.
Otherwise, the subtype depends on the encoding scheme of the result. If the encoding scheme of the result is not Unicode and the field MIXED DATA on installation panel DSNTIPF is NO, the subtype of the result is SBCS. Otherwise, the subtype of the result is mixed.

Casting constant values to DECFLOAT: To cast a constant value, where the value is negative zero, or a floating point constant to DECFLOAT, specify the value as a character string constant rather than a numeric constant. For example:

DECFLOAT('-0')               -- causes DB2 to retain the negative sign for a
                             --   value of negative zero
DECFLOAT('1.00E20')          -- causes DB2 to preserve the precision of the 
                             --   floating point constant

Determining the CCSID and encoding scheme of the result: The CCSID of the result depends on whether the CCSID clause was specified and the context in which the CAST specification was specified.

If the CCSID clause was specified, the CCSID clause is used to determine the CCSID of the result as follows:

If the CCSID clause was specified with EBCDIC, ASCII, or UNICODE, the clause determines the encoding scheme of the result. The CCSID of the result is the appropriate CCSID (from DECP) for that encoding scheme for the data type of the result.
If the CCSID clause was specified with a numeric value representing bit data (65535), the CCSID of the result depends on the data type of the source. If the source data is not string data, the CCSID of the result is the appropriate CCSID for the application encoding scheme. See Note 1 in Determining the encoding scheme and CCSID of a string. If the source is string data, the encoding scheme of the result is the same as the encoding scheme of expression, but the result is considered bit data.
If the CCSID clause was specified with a numeric value, that number is the CCSID of the result. The encoding scheme of the result is determined from the numeric CCSID. In a CAST specification, CCSID 367 refers to ASCII data. For example, assume that MYDATA is string data to be cast to CHAR(10). The following CAST specification returns ASCII SBCS data:
```
CAST(MYDATA AS CHAR(10) CCSID 367)
```
To explicitly cast the data to Unicode SBCS, use the following syntax:
```
CAST(MYDATA AS CHAR(10) CCSID UNICODE 
      FOR SBCS DATA) 
```

If the CCSID clause was not specified, the CCSID of the result is 65535 if the result is bit data. Otherwise, if the data type of the result is a character or graphic string data type, the encoding scheme and CCSID of the result are is determined as follows:

If the expression and data-type are both character, the encoding scheme of the result is the same as expression. For example, assume CHAR_COL is a character column in the following sample:
```
CAST(CHAR_COL AS VARCHAR(25))
```
The result of the CAST is a varying length string with the same encoding scheme as the input. The CCSID of the result is the appropriate CCSID for the encoding scheme and subtype of the result.
If the expression and data-type are both graphic, the encoding scheme and CCSID of the result is the same as expression.
If the result is string and the expression is datetime, the result CCSID is the appropriate CCSID of the expression encoding scheme and the result subtype is the appropriate subtype of the CCSID.
If the result is character, the encoding scheme and CCSID of the result depends on the context in which the CAST specification is specified:
- If the statement follows the rules that are described for type 1 statements in Determining the encoding scheme and CCSID of a string, the CCSID is determined as follows:
  - If the statement references a table or view, the encoding scheme of that table or view determines the encoding scheme for the result.
  - Otherwise, the default EBCDIC encoding scheme is used for the result.
  The CCSID of the result is the appropriate CCSID for the encoding scheme and subtype of the result.
- Otherwise, the CCSID of the result is the appropriate CCSID for the application encoding scheme and subtype of the result.
If the result is graphic, the encoding scheme and the CCSID of the result depends on the context in which the CAST specification is specified:
- If the statement follows the rules that are described for type 1 statements in Determining the encoding scheme and CCSID of a string, the CCSID is determined as follows:
  - If the statement references a table or view, the encoding scheme of that table or view determines the encoding scheme for the result.
  - Otherwise, the default EBCDIC encoding scheme is used for the result.
  The CCSID of the result is the appropriate CCSID for the encoding scheme and data type of the result.
- Otherwise, the CCSID of the result is the appropriate CCSID for the application encoding scheme of the result.
Otherwise, the CCSID of the result depends on the context in which the CAST specification was specified.
- If the statement follows the rules that are described for type 1 in statements in Determining the encoding scheme and CCSID of a string, the CCSID is determined as follows:
  - If the statement references a table or view, the encoding scheme of that table or view determines the encoding scheme for the result.
  - Otherwise, the default EBCDIC encoding scheme is used for the result.
  The CCSID of the result is the appropriate CCSID for the encoding scheme and data type of the result.

Alternative syntax for casting distinct types: There is alternative syntax for casting a distinct type to its source data type and vice versa. Assume that a distinct type D_MONEY was defined with the following statement and column MONEY was defined with that data type.

CREATE TYPE D_MONEY AS DECIMAL(9,2);

DECIMAL(MONEY) is equivalent syntax to CAST(MONEY AS DECIMAL(9,2)). Both forms of the syntax use the cast function that DB2 generated when the distinct type D_MONEY was created to convert the distinct type to its source type of DECIMAL(9,2).

However, it is possible that different cast functions might be chosen for the equivalent syntax forms because of the difference in function resolution, particularly the treatment on unqualified names. Although the process of function resolution is similar for both, in the CAST specification as described above, DB2 uses the schema name of the target data type to locate the function. Therefore, if an unqualified data type name is specified as the target data type, DB2 uses the SQL path to resolve the schema name of the distinct type and then searches for the function in that schema. In function notation, when an unqualified function name is specified, DB2 searches the schemas in the SQL path to find an appropriate function match, as described under Function resolution. For example, assume that you defined the following distinct types, which implicitly gives you both USAGE authority on the distinct types and EXECUTE authority on the cast functions that are generated for them:

CREATE TYPE SCHEMA1.AGE AS DECIMAL(2,0);
   one of the generated cast functions is:
   FUNCTION SCHEMA1.AGE(SYSIBM.DECIMAL(2,0)) RETURNS SCHEMA1.AGE
CREATE TYPE SCHEMA2.AGE AS INTEGER;
   one of the generated cast functions is:
   FUNCTION SCHEMA2.AGE(SYSIBM.INTEGER) RETURNS SCHEMA2.AGE

If STU_AGE, an INTEGER host variable, is cast to the distinct type with either of the following statements and the SQL path is SYSIBM, SCHEMA1, SCHEMA2:

Syntax 1:  CAST(:STU_AGE AS AGE);
Syntax 2:  AGE(:STU_AGE);

different cast functions are chosen. For syntax 1, DB2 first resolves the schema name of distinct type AGE as SCHEMA1 (the first schema in the path that contains a distinct type named AGE for which you have EXECUTE authority for the appropriate generated cast function). Then it looks for a suitable function in that schema and chooses SCHEMA1.AGE because the data type of STU_AGE, which is INTEGER, is promotable to the data type of the function argument, which is DECIMAL(2,0). For syntax 2, DB2 searches all the schemas in the path for an appropriate function and chooses SCHEMA2.AGE. DB2 selects SCHEMA2.AGE over SCHEMA1.AGE because the data type of its argument (INTEGER) is an exact match for STU_AGE (INTEGER) and, therefore, a better match than the argument for SCHEMA1.AGE, which is DECIMAL(2,0).

Start of change Syntax alternatives: TIMEZONE can be specified as an alternative to TIME ZONE. End of change

Example 1: Assume that an application needs only the integer portion of the SALARY column, which is defined as DECIMAL(9,2) from the EMPLOYEE table. The following query for the employee number and the integer value of SALARY could be prepared.

  SELECT EMPNO, CAST(SALARY AS INTEGER) FROM EMPLOYEE;

Example 2: Assume that two distinct types exist in schema SCHEMAX. Distinct type D_AGE was based on SMALLINT and is the data type for the AGE column in the PERSONNEL table. Distinct type D_YEAR was based on INTEGER and is the data type for the RETIRE_YEAR column in the same table. The following UPDATE statement could be prepared.

  UPDATE PERSONNEL SET RETIRE_YEAR =?
                   WHERE AGE = CAST( ? AS SCHEMAX.D_AGE);

The first parameter is an untyped parameter marker that has a data type of RETIRE_YEAR. However, the application will use an integer for the parameter marker. The parameter marker does not need to be cast because the SET is an assignment.

The second parameter marker is a typed parameter marker that is cast to the distinct type D_AGE. Casting the parameter marker satisfies the requirement that comparisons must be performed with compatible data types. The application will use the source data type, SMALLINT, to process the parameter marker.

Example 3: A CAST specification can be used to explicitly specify the data type of a parameter in a context where a parameter marker must be typed. In the following example, the CAST specification is used to tell DB2 to assume that the value that will be provided as input to the TIME function will be CHAR(20). See PREPARE for a list of contexts when invoking functions where parameter markers can be untyped. For all other contexts when invoking a function, the CAST specification can be used to explicitly specify the type of a parameter marker.

  INSERT INTO ADMF001.CASTSQLJ VALUES( TIME(CAST(? AS CHAR(20)) ) )

Example 4: Assume that an application wants to cast an EBCDIC string to Unicode UTF-8. The string contains the value 'Jürgen', which is 6 bytes in ASCII or EBCDIC and is 7 bytes in Unicode UTF-8. In the following query, the CAST specification is invoked with the length clause with CODEUNITS32 specified to ensure that the data is not truncated. (In this case, CODEUNITS16 could also be specified as the string unit.)

   SELECT CAST('Jürgen' AS VARCHAR(6 CODEUNITS32) CCSID UNICODE) 
     FROM SYSIBM.SYSDUMMY1;

For this query, the data is converted from EBCDIC to Unicode UTF-16, the length clause is applied, and then the UTF-16 result is converted to UTF-8.

Example 5: When a keyword is used for a special value that is expressed as a constant in a context where the keyword could be interpreted as a name, the CAST specification can be used to explicitly cast the special value to decimal-floating point. Assume that MYTAB contains columns named C1 and INFINITY, and that you want to reference the decimal float-point value for infinity in the same SQL statement. Use the CAST specification to explicitly cast INFINITY as a decimal floating-point value to ensure that it is not interpreted as the name of a column, parameter or variable:

   SELECT INFINITY                              -- column named INFINITY
    FROM MYTAB
       WHERE C1 = CAST ('INFINITY' AS DECFLOAT) -- comparison is made with the 
                                                -- decimal floating-point 
                                                --  infinity value