DB2 10.5 for Linux, UNIX, and Windows

Cast expressions

A cast expression creates a new value of a specific type based on an existing value.

A cast expression takes two operands: an input expression and a target type. When the cast expression is evaluated, atomization is used to convert the result of the input expression into an atomic value or an empty sequence. If atomization results in a sequence of more than one atomic value, an error is returned. If no errors are returned, the cast expression attempts to create a new value of the target type that is based on the input value. Some combinations of input and target types are not supported for casting. For information about which types can be cast to which other types, see Type casting. When casting a value to a data type, you can use the castable expression to test whether the value can be cast to the data type.

An empty sequence is a valid input value only when the target type is followed by a question mark (?).

If the target type of a cast expression is xs:QName or is a type derived from xs:QName or xs:NOTATION, and input expression is of type xs:string but it is not a literal string, an error is returned.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-Expression--cast as--TargetType-+---+-----------------------><
                                   '-?-'   

Expression
Any XQuery expression that returns a single atomic value or an empty sequence. An empty sequence is allowed when TargetType is followed by a question mark (?).
TargetType
The type to which the value of Expression is cast. TargetType must be an atomic type that is in the predefined atomic XML schema types. The data types xs:NOTATION, xdt:anyAtomicType, and xs:anySimpleType are not valid types for TargetType.
?
Indicates that the result of Expression can be an empty sequence.

Example

In the following example, a cast expression is used to cast the value of the price element, which has the type xs:string, to the type xs:decimal:
for $price in db2-fn:xmlcolumn('PRODUCT.DESCRIPTION')/product/description/price
return $price cast as xs:decimal
When run against the PRODUCT.DESCRIPTION table of the SAMPLE database, the query in the example returns the following result:
9.99
19.99
49.99
3.99