The TRY_CAST function in SQL Server is used to cast value from its existing data type to a specified target data type. This occurs only if the operation is successful. It returns NULL if the conversion fails. TRY_CAST is an extended version of the CAST function. This tutorial explains how to use TRY_CAST in SQL Server.
TRY_CAST considers the value passed and its existing data type and tries to convert it to a specified target data type. If the conversion operation is permitted, it returns the value in the specified target data type. If the operation fails to convert the value, it returns NULL as an output.
TRY_CAST was first included with the Microsoft SQL Server 2012 edition.
Syntax for TRY_CAST
TRY_CAST (Expression as target_data_type [length])
Expression: Value to be cast to a target data type
Target data type: The data type in which the value should be cast
Length: Optional parameter, to specify the length of the datatype
Limitations of Using CAST in SQL Server
When using CAST, we will get an error if the conversion fails from source to target data type. If it is not permitted, this can break the code. Thus, using TRY_CAST instead of CAST is recommended.
In the following example, I am trying to CAST a string value to an Integer, which is an incorrect conversion. CAST fails to complete this conversion and returns an error. However, TRY_CAST returns NULL when the conversion fails to complete.
SELECT CAST('Tech-Recipes' as INT) as CastExample;
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘Tech-Recipes’ to data type int.
Use TRY_CAST to avoid SQL Server raising an error if the data type conversion fails.
SELECT TRY_CAST('Tech-Recipes' as INT) as TryCastExample;
TryCastExample -------------- NULL
Examples Using TRY_CAST
1. Failure using TRY_CAST (returns NULL)
The example below shows that, when it fails, TRY_CAST returns NULL instead of raising an error as CAST does.
SELECT CASE WHEN TRY_CAST('Tech-Recipes.com' AS INT) IS NULL THEN 'Conversion failed' ELSE 'Conversion Successful' END;
2. Successful conversion using TRY_CAST
The example below demonstrates that TRY_CAST completes a successful conversion if it is allowed to convert to a targeted data type.
SELECT CASE WHEN TRY_CAST(1234 AS DECIMAL(18,2)) IS NULL THEN 'Conversion failed' ELSE 'Conversion Successful' END;
3. Explicit conversion using TRY_CAST (not allowed)
The example below demonstrates that TRY_CAST is not allowed to perform an explicit conversion. TRY_CAST throws an error.
Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to xml is not allowed.
Summary
TRY_CAST can be used instead of the CAST function to avoid casting an error without using TRY-CATCH block. The examples used in this tutorial show how to use the TRY_CAST function in SQL Server.
