SQL Server Portal

In my previous post, I discussed about TRY_CONVERT. Today, we will discuss one more excellent conversion function of SQL server namely “TRY_CAST”. Before SQL server 2012, we use “Cast” function very frequently. whenever we need to cast expression from one data type to another data type, we use this function. But there is a problem with this function, if you pass any invalid data to this function and try to cast it into incompatible data type. It will generate an error. TRY_CAST is the solution of this problem.
For Example :

Select Cast ('test' as int)
--Result

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘test’ to data type int.

TRY_CAST requires three parameters expression, data type & Length. Expression and Data type are mandatory but the Length is optional.
Lets discuss this function syntax, purpose and examples in detail.

Syntax :

TRY_CAST ( expression AS data_type [ ( length ) ] )

Purpose :
This function cast expression from one data type to another data type and if it fails to cast, it give result as NULL.

Example-1 : TRY_CAST – Without Error

Select TRY_Cast(123 as int) as [Result]
--Result
123

Example-2 : TRY_CAST – With Error
If TRY_CAST fails, it will return NULL value.

Select TRY_Cast ('test' as int) as [Result]
--Result
NULL

Example-3 : TRY_Cast WITH IIF STATEMENT

In this example , we will try to convert a valid and invalid expression to Integer by using IIF statement.

Select IIF(Try_Cast('test' as int) is NULL , 'Invalid Integer', 'Valid Integer')
--Result
Invalid Integer

Select IIF(Try_Cast(2 as int) is NULL , 'Invalid Integer', 'Valid Integer')
--Result
Valid Integer

Example-4 : TRY_Cast WITH CASE STATEMENT
In this example , we will try to cast a valid and invalid expression to Integer by using case statement.

Select (Case When Try_Cast(123 as int  ) is NULL Then 'Invalid Integer' else 'Valid Integer' end)
--Result
Valid Integer

Select (Case When Try_Cast(123.45 as int) is NULL Then 'Invalid Integer' else  'Valid Integer' end)
--Result
Invalid Integer

Reference : MSDN