5

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.

Community
  • 1
  • 1
JPK
  • 1,324
  • 2
  • 14
  • 25
  • What version of SQL Server are you using? – gbn Mar 12 '14 at 13:44
  • SQL Server Management Studii 2008 R2. Also the alter table statement seems to work on most of the columns, theres only two tables where the float is failing. – JPK Mar 12 '14 at 13:59

1 Answers1

4

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.

ericpap
  • 2,917
  • 5
  • 33
  • 52
  • Marked as solution because I converted the , to . and then the alter table worked on the two tables that failed, but why did it work for many fields with , anyway??? Looks like the ALTER TABLE is a generic way to handle this problem after all, as long as you can modify the data to match the configuration it requires. – JPK Mar 12 '14 at 14:50
  • SQL Server only recognize . as decimal separator. Apparently your string use ,. This is a common problem wich happend in many languages. You could overpass this issue executing the cast after replace the , for . in the string to make sure you don´t have problems converting values. – ericpap Mar 12 '14 at 15:00
  • I have another table as of today and the , to . trick isnt working on it. I just imported the table in a different way, but the original question still stands how to convert these things generically so I dont need to manually alter the data? – JPK Mar 13 '14 at 09:09
  • I would advice you to créate a new field (Float) instead of alter the existing field. Then execute an update to fill this new field with the converted data, making the replace of the , with the . before the cast. If you want i'll give some example of the update query. – ericpap Mar 13 '14 at 10:33
  • Yes this particular task is finished, but it would be good to see how other people do it. – JPK Mar 13 '14 at 11:39