Kyle B. Kyle B. - 7 months ago 12
SQL Question

SQL IsNumeric Returns True but SQL Reports 'Conversion Failed'

Assuming the following data:

Column1 (data type: varchar(50))
--------
11.6
-1
1,000
10"
Non-Numeric String


I have a query,which is pulling data from this column and would like to determine if the value is a number, then return it as such in my query. So I am doing the following

SELECT CASE WHEN IsNumeric(Replace(Column1,'"','')) = 1 Then Replace(Column1,'"','') Else 0 End As NumericValue


SQL is reporting back:
Conversion failed when converting the varchar value '11.6' to data type int.

Why? I have also tried to force cast this:

SELECT CASE WHEN IsNumeric(Replace(Column1,'"','')) = 1 Then cast(Replace(Column1,'"','') as float) Else 0 End As NumericValue


And I got: Error converting data type varchar to float.

Answer

You need to replace comma with a period:

CAST(REPLACE(column, ',', '.') AS FLOAT)

SQL Server outputs decimal separator defined with locale, but does not unterstand anything but a period in CASTs to numeric types.