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.
TRY_CAST ( expression AS data_type [ ( length ) ] )
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