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.
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.
In a Unicode database, character and graphic string source values can be cast to between different string units. Any truncation is applied according to the string units of the target data type.
The following casts involving strongly typed distinct types are supported (using the CAST specification unless noted otherwise):
- Cast from distinct type DT to its source data type S
- Cast from the source data type S of distinct type DT to distinct type DT
- Cast from distinct type DT to the same distinct type DT
- Cast from a data type A to distinct type DT where A is promotable to the source data type S of distinct type DT
- Cast from an INTEGER to distinct type DT with a source data type SMALLINT
- Cast from a DOUBLE to distinct type DT with a source data type REAL
- Cast from a DECFLOAT to distinct type DT with a source data type of DECFLOAT
- Cast from a VARBINARY to distinct type DT with a source data type BINARY
- Cast from a VARCHAR to distinct type DT with a source data type CHAR
- Cast from a VARGRAPHIC to distinct type DT with a source data type GRAPHIC
- For a Unicode database, cast from a VARCHAR or a VARGRAPHIC to distinct type DT with a source data type CHAR or GRAPHIC
- Cast from a distinct type DT with a source data type S to XML using the XMLCAST specification
- Cast from an XML to a distinct type DT with a source data type of any built-in data type, using the XMLCAST specification depending on the XML schema data type of the XML value
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.
- 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.
| 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
I N A R Y |
V
A R B I N A R Y |
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 | Y |
| INTEGER | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | - | Y1 | Y1 | - | - | - | - | - | - | - | Y3 | Y |
| BIGINT | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | - | Y1 | Y1 | - | - | - | - | - | - | - | Y3 | Y |
| 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 | Y | Y | Y1 | Y | Y | Y | Y | Y | Y | Y4 | Y |
| CHAR FOR BIT DATA | Y | Y | 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 | Y | Y | Y1 | Y | Y | Y | Y | Y | Y | Y4 | Y |
| VARCHAR FOR BIT DATA | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | - | - | - | - | Y | Y | Y | Y | Y | Y | Y3 | - |
| CLOB | - | - | - | - | - | - | - | Y | - | Y | - | Y | Y1 | Y1 | Y1 | Y | Y | Y | - | - | - | Y4 | - |
| GRAPHIC | Y1 | Y1 | Y1 | Y1 | Y1 | Y1 | Y1 | Y1 | - | Y1 | - | Y1 | Y | Y | Y | Y | Y | Y | Y1 | Y1 | Y1 | Y3 | Y1 |
| VARGRAPHIC | Y1 | Y1 | Y1 | Y1 | Y1 | Y1 | Y1 | Y1 | - | Y1 | - | Y1 | Y | Y | Y | Y | Y | Y | Y1 | Y1 | Y1 | Y3 | Y1 |
| DBCLOB | - | - | - | - | - | - | - | Y1 | - | Y1 | - | Y1 | Y | Y | Y | Y | Y | Y | - | - | - | Y3 | - |
| BINARY | - | - | - | - | - | - | - | - | Y | - | Y | - | - | - | - | Y | Y | Y | - | - | - | - | - |
| VARBINARY | - | - | - | - | - | - | - | - | Y | - | Y | - | - | - | - | Y | Y | Y | - | - | - | - | - |
| BLOB | - | - | - | - | - | - | - | - | Y | - | Y | - | - | - | - | 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 | Y | Y | Y | - | - | - | - | Y | - | Y | - | - | Y1 | Y1 | - | - | - | - | - | - | - | - | Y |
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 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. |
|||||||||||||||||||||||
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 | SMALLINT scalar function. |
| INTEGER | INTEGER scalar function. |
| BIGINT | BIGINT scalar function. |
| DECIMAL | DECIMAL or DEC scalar function |
| NUMERIC | DECIMAL or DEC scalar function |
| REAL | REAL scalar function |
| DOUBLE | DOUBLE_PRECISION or 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 |
| BINARY | BINARY scalar function |
| VARBINARY | VARBINARY 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 | BOOLEAN scalar function |
Casting 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 |
| BOOLEAN | Y | xs:boolean |
| 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.
| 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 |
| BOOLEAN | Y | xs:boolean |
| 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 XML
Schema 1.0 is supported, 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. |
||
- XML
values that are to be converted to string types other than CHAR and
VARCHAR must fit within the length limits of those data 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, CLOB_1048576
if the XMLCAST target data type is CLOB(1M) .
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.
- XML values that are to be converted to DECIMAL values must have no more than (precision - scale) digits before the decimal point; excess digits after the decimal point beyond the scale are truncated. 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.