DB2 Version 9.7 for Linux, UNIX, and Windows

Casting between data types

There are many occasions where a value with a given data type needs to be cast to a different data type or to the same data type with a different length, precision, or scale. Data type promotion is one example where the promotion of one data type to another data type requires that the value be cast to the new data type. A data type that can be cast to another data type is castable from the source data type to the target data type.

The casting of one data type to another can occur implicitly or explicitly. The cast functions, CAST specification, or XMLCAST specification can be used to explicitly change a data type, depending on the data types involved. In addition, when a sourced user-defined function is created, the data types of the parameters of the source function must be castable to the data types of the function that is being created.

The supported casts between built-in data types are shown in Table 1. 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 operation. A 'Y' indicates that the CAST specification can be used for the combination of source and target data types. Cases in which only the XMLCAST specification can be used are noted.

If truncation occurs when any data type is cast to a character or graphic data type, a warning is returned if any non-blank characters are truncated. This truncation behavior is unlike the assignment to a character or graphic data type, when an error occurs if any non-blank characters are truncated.

The following casts involving distinct types are supported (using the CAST specification unless noted otherwise):

FOR BIT DATA character types cannot be cast to CLOB.

For casts that involve an array type as a target, the data type of the elements of the source array value must be castable to the data type of the elements of the target array data (SQLSTATE 42846). If the target array type is an ordinary array, the source array value must be an ordinary array (SQLSTATE 42821) and the cardinality of the source array value must be less than or equal to the maximum cardinality of the target array data type (SQLSTATE 2202F). If the target array type is an associative array, the data type of the index for the source array value must be castable to data type of the index for the target array type. A user-defined array type value can only be cast to another user-defined array type with the same name (SQLSTATE 42846).

A cursor type cannot be either the source data type or the target data type of a CAST specification, except to cast a parameter marker to a cursor type.

For casts that involve a row type as a target, the degree of the source row value expression and degree of the target row type must match and each field in the source row value expression must be castable to the corresponding target field. A user-defined row type value can only be cast to another user-defined row-type with the same name (SQLSTATE 42846).

It is not possible to cast a structured type value to something else. A structured type ST should not need to be cast to one of its supertypes, because all methods on the supertypes of ST are applicable to ST. If the desired operation is only applicable to a subtype of ST, use the subtype-treatment expression to treat ST as one of its subtypes.

When a user-defined data type involved in a cast is not qualified by a schema name, the SQL path is used to find the first schema that includes the user-defined data type by that name.

The following casts involving reference types are supported:
  • cast from reference type RT to its representation data type S
  • cast from the representation data type S of reference type RT to reference type RT
  • cast from reference type RT with target type T to a reference type RS with target type S where S is a supertype of T.
  • cast from a data type A to reference type RT, where A is promotable to the representation data type S of reference type RT.

When the target type of a reference data type involved in a cast is not qualified by a schema name, the SQL path is used to find the first schema that includes the user-defined data type by that name.

Table 1. Supported Casts between Built-in Data Types
Source Data Type Target Data Type  

S
M
A
L
L
I
N
T

I
N
T
E
G
E
R

B
I
G
I
N
T

D
E
C
I
M
A
L

R
E
A
L

D
O
U
B
L
E

D
E
C
F
L
O
A
T

C
H
A
R

C
H
A
R

F
B
D2

V
A
R
C
H
A
R

V
A
R
C
H
A
R

F
B
D2

C
L
O
B

G
R
A
P
H
I
C

V
A
R
G
R
A
P
H
I
C

D
B
C
L
O
B

B
L
O
B

D
A
T
E

T
I
M
E

T
I
M
E
S
T
A
M
P

X
M
L

B
O
O
L
E
A
N

SMALLINT Y Y Y Y Y Y Y Y Y Y Y - Y1 Y1 - - - - - Y3 Y7
INTEGER Y Y Y Y Y Y Y Y Y Y Y - Y1 Y1 - - - - - Y3 Y7
BIGINT Y Y Y Y Y Y Y Y Y Y Y - Y1 Y1 - - - - - Y3 Y7
DECIMAL Y Y Y Y Y Y Y Y Y Y Y - Y1 Y1 - - - - - Y3 -
REAL Y Y Y Y Y Y Y Y Y Y Y - Y1 Y1 - - - - - Y3 -
DOUBLE Y Y Y Y Y Y Y Y Y Y Y - Y1 Y1 - - - - - Y3 -
DECFLOAT Y Y Y Y Y Y Y Y Y Y Y - Y1 Y1 - - - - - - -
CHAR Y Y Y Y Y Y Y Y Y Y Y Y Y1 Y1 Y1 Y Y Y Y Y4 -
CHAR FOR BIT DATA Y Y Y Y Y Y Y Y Y Y Y - - - - Y Y Y Y Y3 -
VARCHAR Y Y Y Y Y Y Y Y Y Y Y Y Y1 Y1 Y1 Y Y Y Y Y4 -
VARCHAR FOR BIT DATA Y Y Y Y Y Y Y Y Y Y Y - - - - Y Y Y Y Y3 -
CLOB - - - - - - - Y - Y - Y Y1 Y1 Y1 Y - - - Y4 -
GRAPHIC Y1 Y1 Y1 Y1 Y1 Y1 Y1 Y1 - Y1 - Y1 Y Y Y Y Y1 Y1 Y1 Y3 -
VARGRAPHIC Y1 Y1 Y1 Y1 Y1 Y1 Y1 Y1 - Y1 - Y1 Y Y Y Y Y1 Y1 Y1 Y3 -
DBCLOB - - - - - - - Y1 - Y1 - Y1 Y Y Y Y - - - Y3 -
BLOB - - - - - - - - Y - Y - - - - Y - - - Y4 -
DATE - Y Y Y - - - Y Y Y Y - Y1 Y1 - - Y - Y Y3 -
TIME - Y Y Y - - - Y Y Y Y - Y1 Y1 - - - Y - Y3 -
TIMESTAMP - - Y Y - - - Y Y Y Y - Y1 Y1 - - Y Y Y Y3 -
XML Y5 Y5 Y5 Y5 Y5 Y5 Y5 Y5 Y5 Y5 Y5 Y5 Y5 Y5 Y5 Y5 Y5 Y5 Y5 Y -
BOOLEAN Y7 Y7 Y7 - - - - - - - - - - - - - - - - - Y7
Notes
  • See the description preceding the table for information on supported casts involving user-defined types and reference types.
  • It is not possible to cast a structured type value to anything else.
  • The LONG VARCHAR and LONG VARGRAPHIC data types continue to be supported but are deprecated, not recommended, and might be removed in a future release.

1 Cast is only supported for Unicode databases.

2 FOR BIT DATA

3 Cast can only be performed using XMLCAST.

4 An XMLPARSE function is implicitly processed to convert a string to XML on assignment (INSERT or UPDATE) of a string to an XML column. The string must be a well-formed XML document for the assignment to succeed.

5 Cast can only be performed using XMLCAST and depends on the underlying XML schema data type of the XML value. For details, see "XMLCAST".

6 A cursor type cannot be either the source data type or the target data type of a CAST specification, except to cast a parameter marker to a cursor type.

7 Only supported using the CAST specification. No cast function exists.

Table 2 shows where to find information about the rules that apply when casting to the identified target data types.

Table 2. Rules for Casting to a Data Type
Target Data Type Rules
SMALLINT If the source type is BOOLEAN, then TRUE is cast to 1 and FALSE is cast to 0. For all other source types, see SMALLINT scalar function.
INTEGER If the source type is BOOLEAN, then TRUE is cast to 1 and FALSE is cast to 0. For all other source types, see INTEGER scalar function.
BIGINT If the source type is BOOLEAN, then TRUE is cast to 1 and FALSE is cast to 0. For all other source types, see BIGINT scalar function.
DECIMAL DECIMAL scalar function
NUMERIC DECIMAL scalar function
REAL REAL scalar function
DOUBLE DOUBLE scalar function
DECFLOAT DECFLOAT scalar function
CHAR CHAR scalar function
VARCHAR VARCHAR scalar function
CLOB CLOB scalar function
GRAPHIC GRAPHIC scalar function
VARGRAPHIC VARGRAPHIC scalar function
DBCLOB DBCLOB scalar function
BLOB BLOB scalar function
DATE DATE scalar function
TIME TIME scalar function
TIMESTAMP If the source type is a character string, see TIMESTAMP scalar function, where one operand is specified. If the source data type is a DATE, the timestamp is composed of the specified date and a time of 00:00:00.
BOOLEAN If the source type is numeric, then 0 is cast to FALSE and 1 is cast to TRUE. NULL is cast to NULL.

Casting non-XML values to XML values

Table 3. Supported Casts from Non-XML Values to XML Values
Source Data Type Target Data Type
XML Resulting XML Schema Type
SMALLINT Y xs:short
INTEGER Y xs:int
BIGINT Y xs:long
DECIMAL or NUMERIC Y xs:decimal
REAL Y xs:float
DOUBLE Y xs:double
DECFLOAT N -
CHAR Y xs:string
VARCHAR Y xs:string
CLOB Y xs:string
GRAPHIC Y xs:string
VARGRAPHIC Y xs:string
DBCLOB Y xs:string
DATE Y xs:date
TIME Y xs:time
TIMESTAMP Y xs:dateTime1
BLOB Y xs:base64Binary
character type FOR BIT DATA Y xs:base64Binary
distinct type   use this chart with the source type of the distinct type
Notes

1 The source data type TIMESTAMP supports timestamp precision of 0 to 12. The maximum fractional seconds precision of xs:dateTime is 6. If the timestamp precision of a TIMESTAMP source data type exceeds 6, the value is truncated when cast to xs:dateTime.

The LONG VARCHAR and LONG VARGRAPHIC data types continue to be supported but are deprecated, not recommended, and might be removed in a future release.

When character string values are cast to XML values, the resulting xs:string atomic value cannot contain illegal XML characters (SQLSTATE 0N002). If the input character string is not in Unicode, the input characters are converted to Unicode.

Casting to SQL binary types results in XQuery atomic values with the type xs:base64Binary.

Casting XML values to non-XML values

An XMLCAST from an XML value to a non-XML value can be described as two casts: an XQuery cast that converts the source XML value to an XQuery type corresponding to the SQL target type, followed by a cast from the corresponding XQuery type to the actual SQL type.

An XMLCAST is supported if the target type has a corresponding XQuery target type that is supported, and if there is a supported XQuery cast from the source value's type to the corresponding XQuery target type. The target type that is used in the XQuery cast is based on the corresponding XQuery target type and might contain some additional restrictions.

The following table lists the XQuery types that result from such conversion.

Table 4. Supported Casts from XML Values to Non-XML Values
Target Data Type Source Data Type
XML Corresponding XQuery Target Type
SMALLINT Y xs:short
INTEGER Y xs:int
BIGINT Y xs:long
DECIMAL or NUMERIC Y xs:decimal
REAL Y xs:float
DOUBLE Y xs:double
DECFLOAT Y no matching type1
CHAR Y xs:string
VARCHAR Y xs:string
CLOB Y xs:string
GRAPHIC Y xs:string
VARGRAPHIC Y xs:string
DBCLOB Y xs:string
DATE Y xs:date
TIME (without time zone) Y xs:time
TIMESTAMP (without time zone) Y xs:dateTime2
BLOB Y xs:base64Binary
CHAR FOR BIT DATA N not castable
VARCHAR FOR BIT DATA Y xs:base64Binary
distinct type   use this chart with the source type of the distinct type
row, reference, structured or abstract data type (ADT), other N not castable
Notes
1 DB2® supports XML Schema 1.0, which does not provide a matching XML schema type for a DECFLOAT. Processing of the XQuery cast step of XMLCAST is handled as follows:
  • If the source value is typed with an XML schema numeric type, use that numeric type.
  • If the source value is typed with the XML schema type xs:boolean, use xs:double.
  • Otherwise, use xs:string with additional checking for a valid numeric format.

2 The maximum fractional seconds precision of xs:dateTime is 6. The source data type TIMESTAMP supports timestamp precision of 0 to 12. If the timestamp precision of a TIMESTAMP target data type is less than 6, the value is truncated when cast from xs:dateTime. If the timestamp precision of a TIMESTAMP target data type exceeds 6, the value is padded with zeros when cast from xs:dateTime.

In the following restriction cases, a derived by restriction XML schema data type is effectively used as the target data type for the XQuery cast.
  • XML values that are to be converted to string types must fit within the length limits of those DB2 types without truncation of any characters or bytes. The name used for the derived XML schema type is the uppercase SQL type name followed by an underscore character and the maximum length of the string; for example, VARCHAR_20 if the XMLCAST target data type is VARCHAR(20).
  • XML values that are to be converted to DECIMAL values must fit within the precision of the specified DECIMAL values, and must not contain more nonzero digits after the decimal point than the scale. The name used for the derived XML schema type is DECIMAL_precision_scale, where precision is the precision of the target SQL data type, and scale is the scale of the target SQL data type; for example, DECIMAL_9_2 if the XMLCAST target data type is DECIMAL(9,2).
  • XML values that are to be converted to TIME values cannot contain a seconds component with nonzero digits after the decimal point. The name used for the derived XML schema type is TIME.

The derived XML schema type name only appears in a message if an XML value does not conform to one of these restrictions. This type name helps one to understand the error message, and does not correspond to any defined XQuery type. If the input value does not conform to the base type of the derived XML schema type (the corresponding XQuery target type), the error message might indicate that type instead. Because this derived XML schema type name format might change in the future, it should not be used as a programming interface.

Before an XML value is processed by the XQuery cast, any document node in the sequence is removed and each direct child of the removed document node becomes an item in the sequence. If the document node has multiple direct children nodes, the revised sequence will have more items than the original sequence. The XML value without any document nodes is then atomized using the XQuery fn:data function, with the resulting atomized sequence value used in the XQuery cast. If the atomized sequence value is an empty sequence, a null value is returned from the cast without any further processing. If there are multiple items in the atomized sequence value, an error is returned (SQLSTATE 10507).

If the target type of XMLCAST is the SQL data type DATE, TIME, or TIMESTAMP, the resulting XML value from the XQuery cast is also adjusted to UTC, and the time zone component of the value is removed.

When the corresponding XQuery target type value is converted to the SQL target type, binary XML data types, such as xs:base64Binary or xs:hexBinary, are converted from character form to actual binary data.

If an xs:double or xs:float value of INF, -INF, or NaN is cast (using XMLCAST) to an SQL data type DOUBLE or REAL value, an error is returned (SQLSTATE 22003). An xs:double or xs:float value of -0 is converted to +0.

The target type can be a user-defined distinct type if the source operand is not a user-defined distinct type. In this case, the source value is cast to the source type of the user-defined distinct type (that is, the target type) using the XMLCAST specification, and then this value is cast to the user-defined distinct type using the CAST specification.

In a non-Unicode database, casting from an XML value to a non-XML target type involves code page conversion from an internal UTF-8 format to the database code page. This conversion will result in the introduction of substitution characters if any code point in the XML value is not present in the database code page.