Feeds:
Posts
Comments

Posts Tagged ‘TRY_CAST’

SQL SERVER 2012– Convert Text to Numbers

Posted in Solutions, SQL SERVER, tagged , , , , , , , on October 14, 2012| Leave a Comment »

In this article, we will convert text to number in multiple versions of SQL Server and will see the difference. I will use four different Data conversion functions (Convert, Cast, Try_Convert &  Try_Cast) to convert Text to Number.

Let me explain this with simple examples.

In SQL Server 2005/2008 :

Example 1 : (Convert Text to integer if text having numeric data only)

Declare @string as varchar(5)
Set @string ='12345'

Select Convert(int,@string) as [Convert Text to Integer]
Select Cast(@string as int) as [Cast Text to Integer]

--RESULT
Convert Text to Integer
-----------------------
12345

(1 row(s) affected)

Cast Text to Integer
--------------------
12345

(1 row(s) affected)

Example 2 : (Convert Text to integer if text having string data)


Declare @string as varchar(7)
Set @string ='raresql'

Select Convert(int,@string) as [Convert Text to Integer]
Select Cast(@string as int) as [Cast Text to Integer]
--RESULT
Convert Text to Integer
-----------------------

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

In SQL Server 2012 :

Example 3 : (Convert Text to integer if text having numeric data only)

Declare @string as varchar(5)
Set @string ='12345'

Select Try_Convert(int,@string) as [Convert Text to Integer]
Select Try_Cast(@string as int) as [Cast Text to Integer]

--RESULT
Convert Text to Integer
-----------------------
12345

(1 row(s) affected)

Cast Text to Integer
--------------------
12345

(1 row(s) affected)

Example 4 : (Convert Text to integer if text having string data)


Declare @string as varchar(7)
Set @string ='raresql'

Select Try_Convert(int,@string) as [Convert Text to Integer]
Select Try_Cast(@string as int) as [Cast Text to Integer]
--RESULT
Convert Text to Integer
-----------------------
NULL

(1 row(s) affected)

Cast Text to Integer
--------------------
NULL

(1 row(s) affected)

Conclusion :
If we compare Examples 1 & 3, we can easily find out that there is no difference, but we can see that Examples 2 and 4 having much difference because Cast & Convert generates an error if there is a compatibility issue with data type, but Try_Convert & Try_Cast (SQL Server 2012) will return NULL instead of error.

Read Full Post »

SQL SERVER 2012 -Conversion Function -TRY_CAST

Posted in Functions, SQL SERVER, tagged , , , on August 28, 2012| Leave a Comment »

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

Read Full Post »