SQL convert varchar column to float

问题:

An importing tool put every column in several tables as varchar(max) and I am looking for the fastest way to convert the columns to the proper type.

This works fine,

ALTER TABLE dbo.myTable ALTER COLUMN myColumn INT

but this fails miserably

ALTER TABLE dbo.myTable ALTER COLUMN myColumn FLOAT

With error: Msg 8114, Level 16, State 5, Line 26 Error converting data type varchar to float.

How do I perform a generic column conversion from type VarChar(max) to Float?

---

I found some hints in these posts, but I have not been able to get it to convert:

Convert varchar to float IF ISNUMERIC

Error converting data type varchar

error converting varchar to float

select case isnumeric([myColumn]) when 1 then cast([myColumn] as float) else null end
from dbo.myTable

and

SELECT TOP (100) CAST(CASE WHEN IsNumeric([myColumn]) = 1 THEN [myColumn] ELSE NULL END AS float) AS [myColumn]
FROM         dbo.myTable
WHERE     ([myColumn] NOT LIKE '%[^0-9]%')

It seems there is some issues with ISNUMERIC?

I would prefer not to do it like this as there is a lot of columns to edit, is there a single line conversion like ALTER TABLE that works in most or all scenarios?

Alternatively someone has suggested holding the data into a temp column? If someone could post code to do this into a tempColumn and then convert the original and delete the tempColumn that would be another valid solution.

回答1:

Are the decimal separator of the system configured the same way as content from varchar field? In your case i will advice you to créate a new column(FLOAT) instead of alter existing column. Then fill that field with an update.

Here is a way to explicit convert text to float. IN SQL Server this query :

select cast(replace('12,5',',','.') as float)*2

Resutns 25 as response. That means is converted successfull to FLOAT

So to fill you new col with casted values you could do:

UPDATE Table SET FloatField=CAST(REPLACE(TextField,',','.') AS FLOAT)

That replace the , for . (In case there is any) and then converts to FLOAT. Hope this help.