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. 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
- 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.
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:
To explicitly cast the data to Unicode SBCS, use the following syntax:CAST(MYDATA AS CHAR(10) CCSID 367)
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:
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.CAST(CHAR_COL AS VARCHAR(25))
- 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.
- Otherwise, the CCSID of the result is the appropriate CCSID for the application encoding scheme and subtype of the result.
- 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 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.
- Otherwise, the CCSID of the result is the appropriate CCSID for the application encoding scheme of the result.
- 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:
- 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.
- 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:
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).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
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).Syntax alternatives: TIMEZONE can be specified as an alternative to TIME ZONE.
SELECT EMPNO, CAST(SALARY AS INTEGER) FROM EMPLOYEE;
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.
INSERT INTO ADMF001.CASTSQLJ VALUES( TIME(CAST(? AS CHAR(20)) ) )
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. SELECT INFINITY -- column named INFINITY
FROM MYTAB
WHERE C1 = CAST ('INFINITY' AS DECFLOAT) -- comparison is made with the
-- decimal floating-point
-- infinity value