Start of change

TRY_CAST specification

Edit online

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.

Read syntax diagramSkip visual syntax diagramTRY_CAST( expressionsearch-conditionNULLparameter-marker ASdata-type)
data-type
Read syntax diagramSkip visual syntax diagrambuilt-in-typedistinct-type
built-in-type
Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(5,0)(integer,0, integer)FLOAT(53)(integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)(integer)CHARACTERCHARVARYINGVARCHAR(integer)FOR BIT DATAFOR SBCS DATAFOR MIXED DATAccsid-clauseCHARACTERCHARLARGE OBJECTCLOB(1M)(integerKMG)FOR SBCS DATAFOR MIXED DATAccsid-clauseGRAPHIC(1)(integer)GRAPHIC VARYINGVARGRAPHIC(integer)DBCLOB(1M)(integerKMG)ccsid-clauseNATIONAL CHARACTERNATIONAL CHARNCHAR(1)(integer)NATIONAL CHARACTERNATIONAL CHARNCHARVARYINGNVARCHAR(integer)NATIONAL CHARACTERNCHARLARGE OBJECTNCLOB(1M)(integerKMG)normalize-clauseBINARY(1)(integer)BINARY VARYINGVARBINARY(integer)BLOBBINARY LARGE OBJECT(1M)(integerKMG)DATETIME(0)TIMESTAMP(6)(integer)DATALINK(200)(integer)ccsid-clauseROWIDXMLccsid-clauseBOOLEAN
ccsid-clause
Read syntax diagramSkip visual syntax diagramCCSIDintegernormalize-clause
normalize-clause
Read syntax diagramSkip visual syntax diagramNOT NORMALIZEDNORMALIZED

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
End of change