SQL TRY_CAST Function

The TRY_CAST function converts values from one data type to another.

CAST supports limited formatting of the return values.

If the conversion fails, instead of returning an error, a NULL is returned.

Example

#

This example converts different data types to other data types.
If the conversion fails, a NULL is returned.

SELECT TRY_CAST(1859 AS CHAR(4)) AS '1859',
       TRY_CAST('1859.5' AS INT) AS '1859.5',
       TRY_CAST('1859AB' AS DECIMAL(10, 2)) AS '1859AB'
Result:  1 record
1859 1859.5 1859AB
1859 NULL NULL

Syntax

Syntax of the TRY_CAST function.

TRY_CAST(value AS data-type [(length)])

value -- a value to be cast to another data type.

data-type -- a data type to cast the value.

length -- optional, represents the length of the data-type.

More Examples

TRY_CAST with CASE

Problem: Cast the value to integer. If the cast succeeds, return 'Successful', otherwise 'Failed', rather than NULL.
SELECT CASE
         WHEN TRY_CAST('1859AB' AS INT) IS NULL
         THEN 'Failed'
         ELSE 'Successful'
       END AS 'Cast Status'
Result:  1 record
Cast Status
Failed

You may also like



Guides


vsn 3.1