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]--RESULTConvert 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]--RESULTConvert 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] --RESULTConvert 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]--RESULTConvert 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.