How to Use the TRY_CAST Function in SQL Server

feature-sql1-618x350

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.

CAST_Function_SQL_Server

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

 

TRY_CAST_Function_SQL_Server

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;

 

TRY_CAST_Function_SQL_Server_Failure

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;

 

TRY_CAST_Function_SQL_Server_Successful

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.

TRY_CAST_Function_SQL_Server_Failure_Explicit_Conversion

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.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.