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 header row 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 strongly typed distinct types are supported (using the CAST specification unless noted otherwise):
For casts involving a weakly typed distinct type as a target, the cast from data type must be castable to the source type of the weakly typed distinct type and the data type constraints must evaluate to true or unknown for the value. The only context in which an operand is implicitly cast to a weakly typed distinct type is when a source operand is assigned to a target with a data type that is a weakly typed distinct type.
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 be cast only to the same user-defined array type (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 required 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.
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.
Source Data Type | Target Data Type | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
S |
I |
B |
D |
R |
D |
D |
C |
C |
V |
V |
C |
G |
V |
D |
B |
D |
T |
T |
X |
B |
|
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
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.
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. |
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.
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.
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:
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. |
If an XML value is converted to a CHAR or VARCHAR type that is too small to contain all of the data, the data is truncated to fit the specified data type and no error is returned. If any non-blank characters are truncated, a warning (SQLSTATE 01004) is returned. If truncation of the value leads to the truncation of a multibyte character, the whole multibyte character is removed. Therefore, in some cases, truncation can produce a shorter string than expected. For example, the character, ñ, is represented in UTF-8 by 2 bytes, 'C3 B1'. When this character is cast as VARCHAR(1), the truncation of 'C3 B1' to 1 byte would leave a partial character of 'C3'. This partial character, 'C3', is also removed, therefore the final result is an empty string.
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.