TRY_CAST specification
The TRY_CAST specification returns the cast operand (the first operand) cast to the type specified by the data-type. The syntax and semantics of TRY_CAST are identical to CAST except when the cast operation is not successful, a null value is returned.
The TRY_CAST specification is identical to the CAST specification except for the handling of error conditions. When a value cannot be converted successfully to the result data type, for example trying to cast the string 'ABC' to a numeric value, the null value is returned instead of a mapping error.
For the detailed syntax and semantic rules, see CAST specification.
The result can be null. If expression is null or if the conversion to the result data type is not successful, the result is the null value.
Example
- A varying length character column, VC_DATA, contains values that are intended to be integer data
values. TRY_CAST is used to identify and handle any invalid data. Using CASE causes 0 to be returned
for the unexpected values.
SELECT CASE WHEN TRY_CAST(VC_DATA AS INTEGER) IS NULL THEN 0 ELSE VC_DATA END FROM TESTDATA