CAST specification

Description for CAST specification

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 any data type is cast to a character for graphic data type, a warning if any non-blank characters 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.

SMALLINT

For a small integer.

INTEGER or INT

For a large integer.

BIGINT

For a big integer.

DECIMAL(integer,integer) or DEC(integer,integer)

DECIMAL(integer) or DEC(integer)

DECIMAL or DEC

For a decimal number. The first integer is the precision of the number. That is, the total number of digits 1–31. The second integer is the scale of the number. That is, the number of digits to the right of the decimal point, which can range from 0 to the precision of the number.

You can use DECIMAL(p) for DECIMAL(p,0) and DECIMAL for DECIMAL(5,0).

You can also use the word NUMERIC instead of DECIMAL. For example, NUMERIC(8) is equivalent to DECIMAL(8). Unlike DECIMAL, NUMERIC has no allowable abbreviation.

DECFLOAT( integer)

For a decimal floating-point number. The value of integer must be either 16 or 34 and represents the number of significant digits that can be stored. If integer is omitted, the DECFLOAT value can represent 34 significant digits.

FLOAT(integer)

FLOAT

For a floating-point number. If integer is in the range 1– 21 inclusive, the format is single precision floating-point. If the integer is in the range 22–53 inclusive, the format is double precision floating-point.

You can use DOUBLE PRECISION or FLOAT for FLOAT(53).

For portability across operating systems, when specifying a floating-point data type, use REAL or DOUBLE instead of FLOAT.

REAL

For single precision floating-point.

DOUBLE or DOUBLE PRECISION

For double precision floating-point

CHARACTER(integer) or CHAR(integer)

CHARACTER or CHAR

For a fixed-length character string of length integer, which can range 1–255. If the length specification is omitted, a length of 1 character is assumed.

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. For more information, see String unit specifications.

VARCHAR(integer), CHAR VARYING(integer), or CHARACTER VARYING(integer)

For a varying-length character string of maximum length integer in the range 1–32764.

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. For more information, see String unit specifications.

CLOB(integer [K|M|G]), CHAR LARGE OBJECT(integer [K|M|G]), or CHARACTER LARGE OBJECT(integer [K|M|G])

CLOB, CHAR LARGE OBJECT, or CHARACTER LARGE OBJECT

For a character large object (CLOB) string of the specified maximum length in bytes. The maximum length must be in the range 1–2147483647.

When integer is not specified, the default length is 1 M. The maximum value that can be specified for integer depends on whether a units indicator is also specified as shown in the following list.

integer

The maximum value for integer is 2147483647. The maximum length of the string is integer.

integer K

The maximum value for integer is 2097152. The maximum length is 1024 times integer.

integer M

The maximum value for integer is 2048. The maximum length is 1,048,576 times integer.

integer G

The maximum value for integer is 2. The maximum length is 1,073,741,824 times integer.

integer can be separated from K, M, or G by 0 or more spaces.

If you specify a value that evaluates to 2 gigabytes (2,147,483,648), Db2 uses a value that is one byte less, or 2147483647.

You can specify that the length of the result be evaluated in a specific number of string units: CODEUNITS16, CODEUNITS32, or OCTETS. For more information, see String unit specifications.

GRAPHIC(length)

GRAPHIC

For a fixed-length graphic string of length integer, which can range 1–127. If the length specification is omitted, a length of 1 character is assumed.

You can specify that the length of the result be evaluated in a specific number of string units: CODEUNITS16 or CODEUNITS32. For more information, see String unit specifications.

VARGRAPHIC(integer)

For a varying-length graphic string of maximum length integer in the range 1–32764.

You can specify that the length of the result be evaluated in a specific number of string units: CODEUNITS16 or CODEUNITS32. For more information, see String unit specifications.

DBCLOB(integer [K|M|G])

DBCLOB

For a double-byte character large object (DBCLOB) string of the specified maximum length in double-byte characters. The maximum length must be in the range 1–1,073,741,823.

When integer is not specified, the default length is 1M. The meaning of integer K|M|G is similar to CLOB. The difference is that the number specified is the number of double-byte characters.

integer can be separated from K, M, or G by 0 or more spaces.

You can specify that the length of the result be evaluated in a specific number of string units: CODEUNITS16, CODEUNITS32, or OCTETS. For more information, see String unit specifications.

BINARY(integer)

A fixed-length binary string of length integer. The integer can range 1–255. If the length specification is omitted, a length of 1 byte is assumed.

BINARY VARYING(integer) or VARBINARY(integer)

A varying-length binary string of maximum length integer in the range 1–32764.

BLOB (integer [K|M|G] or BINARY LARGE OBJECT(integer [K|M|G])

BLOB or BINARY LARGE OBJECT

For a binary large object (BLOB) string of the specified maximum length in bytes. The maximum length must be in the range 1–2147483647.

When integer is not specified, the default length is 1M. The meaning of integer K|M|G is the same as for CLOB.

integer can be separated from K, M, or G by 0 or more spaces.

DATE

For a date.

TIME

For a time.

TIMESTAMP(integer) WITHOUT TIME ZONE

For a timestamp. integer specifies the optional timestamp precision attribute and must be in the range 0–12. The timestamp precision denotes the number of fractional second digits that are included in the timestamp. The default is 6.

TIMESTAMP(integer) WITH TIME ZONE

For a timestamp with time zone. integer specifies the optional timestamp precision attribute and must be in the range 0–12. The timestamp precision denotes the number of fractional second digits that are included in the timestamp. The default is 6.

• 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 base data type of the distinct type.
  • If the target data type is an array type:
    • The elements in the source array value must be castable to the data type of the elements of the target array type. The index values for the source array value must be castable to the data type of the index of the target array type.
    • If the target array type is an ordinary array, the cardinality of the source array value must be less than or equal to the maximum cardinality of the target array type.
  • Otherwise, any data type if valid.

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.

FOR subtype DATA

Specifies a subtype for a character string value, which has a data type of CHAR, VARCHAR, or CLOB. Do not use the FOR subtype DATA clause with values of any other data type (including any distinct type). subtype is one of the following keywords:

SBCS

The value contains single-byte data.

MIXED

The value contains mixed data. Do not specify MIXED if the value of MIXED DECP value is NO.

BIT

Column holds BIT data. Do not specify BIT for a CLOB value. Only character strings are valid when subtype is BIT.

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.

Notes for CAST specification

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 scalar function.) 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 scalar function.)

If the data type of the result is character, and the FOR subtype DATA clause is not specified, the subtype of the result is determined by the following rules:

• 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 MIXED DECP value 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 encoding scheme is EBCDIC, the default EBCDIC CCSID is used, and the subtype depends on the MIXED DECP value.
    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 base 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).

Syntax alternatives for time zones

: TIMEZONE can be specified as an alternative to TIME ZONE.