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