Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – How to convert varchar to float’

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

varchartofloat1.1
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

varchartofloat1.2 Cleaning :

use tempdb
GO
drop table tbl_test

Read Full Post »