If you are planning to convert varchar to float you should know that these two data types are not compatible with each other. In the earlier versions of SQL Server you had to use CASE, ISNUMERIC & CONVERT to convert varchar to float but in SQL Server 2012, you can do it with just one function TRY_CONVERT. Let me create a sample to explain it.
Use tempdb GO Create Table tbl_test ( [Numbers] varchar(50) ) GO Insert into tbl_test values(12345.6789) Insert into tbl_test values('AB12345') Insert into tbl_test values(12.1) Insert into tbl_test values(11.2345678) GO
Given below are two different methods to convert varchar to float but these two methods are compatible with different versions of SQL Server.
Method 1 (For earlier versions of SQL Server):
--This script is compatible with SQL Server 2005 and above. Use tempdb GO Select [Numbers], (Case When Isnumeric([Numbers])=1 Then Convert(float,[Numbers]) else NULL end) As [Varchar to Float] from tbl_test --OUTPUT
Method 2 (For SQL Server 2012):
--This script is compatible with SQL Server 2012 and above. Use tempdb GO Select Try_convert(float,[Numbers]) as [Varchar to float] from tbl_test --OUTPUT
use tempdb GO drop table tbl_test
It was very usefull, thanks!
This is helpful. Had problems importing flat files and getting conversion errors.
Thank you.
very useful. thanks
Very helpful.
thanks a lot solved the issue