Data Conversion

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 (ConvertCastTry_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.

Leave a Reply

Your email address will not be published. Required fields are marked *