CASTABLE predicate (convertibility check)

This checks whether an expression can be converted to a particular data type.

The CASTABLE predicate enables you to check whether a corresponding CAST expression (see section "CAST expression") can be executed before it is executed and to react appropriately.

---

expression IS [NOT] CASTABLE AS data_type

---

expression

CAST operand. The value of expression may not be a multiple value with a dimension > 1.

data_type

Target data type for the result of the corresponding CAST expression.
data_type may not contain a dimension for a multiple column.

It must be possible to combine the data type of expression with data_type, see the table 23.

Result

Without NOT:

True if expression can be converted to the specified data type.
False if expression cannot be converted to the specified data type.

With NOT:

True if expression cannot be converted to the specified data type.
False if expression can be converted to the specified data type.

Example

Check whether an entry can be converted to a numeric data type with a particular length.

CASE WHEN :input IS CASTABLE AS NUMERIC(7,2)
     THEN CAST :input AS NUMERIC(7,2)
     ELSE -1
END