CAST specification

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

Read syntax diagramSkip visual syntax diagramCAST( expressionNULLparameter-marker ASdata-type)
data-type
Read syntax diagramSkip visual syntax diagrambuilt-in-typedistinct-typearray-type
built-in-type
Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(5,0)(integer,0, integer)FLOAT(53)(integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)(integer)CHARACTERCHARVARYINGVARCHAR(integer)FOR BIT DATAFOR SBCS DATAFOR MIXED DATAccsid-clauseCHARACTERCHARLARGE OBJECTCLOB(1M)(integerKMG)FOR SBCS DATAFOR MIXED DATAccsid-clauseGRAPHIC(1)(integer)GRAPHIC VARYINGVARGRAPHIC(integer)DBCLOB(1M)(integerKMG)ccsid-clauseNATIONAL CHARACTERNATIONAL CHARNCHAR(1)(integer)NATIONAL CHARACTERNATIONAL CHARNCHARVARYINGNVARCHAR(integer)NATIONAL CHARACTERNCHARLARGE OBJECTNCLOB(1M)(integerKMG)normalize-clauseBINARY(1)(integer)BINARY VARYINGVARBINARY(integer)BLOBBINARY LARGE OBJECT(1M)(integerKMG)DATETIME(0)TIMESTAMP(6)(integer)DATALINK(200)(integer)ccsid-clauseROWIDXMLccsid-clause
ccsid-clause
Read syntax diagramSkip visual syntax diagramCCSIDintegernormalize-clause
normalize-clause
Read syntax diagramSkip visual syntax diagramNOT NORMALIZEDNORMALIZED

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 Casting between data types, 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 name, type, and variables. 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.

  • For a cast operand that is an expression, see Table 2 for the target data types that are supported based on the data type of the cast operand.
  • For a cast operand that is the keyword NULL, the target data type can be any data type.
  • For a cast operand that is a parameter marker, the target data type can be any data type. If the data type is a distinct type, the application that uses the parameter marker will use the source data type of the distinct type. If the data type is an array type, the parameter marker must represent an array with a cardinality less than or equal to the maximum cardinality of the target array data type. The data type of the parameter marker must match the data type of the target array data type exactly.

If the CCSID attribute is not specified, then:

  • If the data-type is BINARY, VARBINARY, or BLOB, a CCSID of 65535 is used.
  • If FOR BIT DATA is specified, a CCSID of 65535 is used.
  • If the expression is a character string, and the data-type is CHAR, VARCHAR, or CLOB:
    • If FOR SBCS DATA is specified,
      • If the CCSID of the expression is a Unicode CCSID, then the single-byte CCSID associated with the default CCSID of the job is used.
      • Otherwise, the single-byte CCSID associated with the CCSID of the expression is used.1
    • If FOR MIXED DATA is specified,
      • If the CCSID of the expression is a Unicode CCSID, then the mixed-byte CCSID associated with the default CCSID of the job is used.
      • Otherwise, the mixed-byte CCSID associated with the CCSID of the expression is used.1
    • Otherwise, the default CCSID of the expression is used.21
  • If the expression is a graphic string or expression is a parameter marker, and the data-type is CHAR, VARCHAR, or CLOB:
    • If FOR SBCS DATA is specified, the single-byte CCSID associated with the default CCSID of the job is used.
    • If FOR MIXED DATA is specified, the mixed-byte CCSID associated with default CCSID of the job is used.
    • Otherwise, Start of change
      • If the default CCSID of the job is SBCS data, the default CCSID of the job is used.
      • If the default CCSID of the job is mixed data and the length attribute of the result is greater than or equal to 4, then the default CCSID of the job is used.
      • If the default CCSID of the job is mixed data that is DBCS-open or DBCS-either and the length attribute of the result is less than 4, the CCSID of the result is the associated SBCS CCSID for the mixed data CCSID.
      End of change
  • If the expression is a character string or parameter marker, and the data-type is GRAPHIC, VARGRAPHIC, DBCLOB, Start of changeDATE, TIME, or TIMESTAMPEnd of change; the CCSID 1200 is used.
  • If the expression is a graphic string, and the data-type is GRAPHIC, VARGRAPHIC, or DBCLOB; the CCSID of the expression is used.
  • If the data-type is XML, the CCSID value as specified by the SQL_XML_DATA_CCSID QAQQINI setting is used. See XML Values for more information.
  • Otherwise, the default CCSID of the job is used.

If the CCSID attribute is specified, the data will be converted to that CCSID. If NORMALIZED is specified, the data will be normalized. Start of changeIf FOR MIXED DATA or a mixed CCSID is specified, the result length cannot be less than 4.End of change

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

Examples

  • An application is only interested in the integer portion of the SALARY column (defined as DECIMAL(9,2)) from the EMPLOYEE table. The following CAST specification will convert the SALARY column to INTEGER. 
    SELECT EMPNO, CAST(SALARY AS INTEGER)
  FROM EMPLOYEE
  • Assume that two distinct types exist. T_AGE was sourced on SMALLINT and is the data type for the AGE column in the PERSONNEL table. R_YEAR was sourced 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 T_AGE )

    The first parameter is an untyped parameter marker that would have a data type of R_YEAR. An explicit CAST specification is not required in this case because the parameter marker value is assigned to the distinct type.

    The second parameter marker is a typed parameter marker that is cast to distinct type T_AGE. An explicit CAST specification is required in this case because the parameter marker value is compared to the distinct type.

1 For XSLTRANSFORM, if the CCSID of the expression is 65535, the default CCSID of the job is used.
2 If the CCSID of the expression is 65535, casting to CLOB is not allowed.