SQL Server Portal

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 »