SQL IsNumeric Returns True but SQL Reports 'Conversion Failed'

Question

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 1

ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type;

So the problem is it is a valid number but not a valid int.

Answer 2

Kyle,

I think this solves the problem. The problem lies in the fact that the ELSE clause initializes your result to be an INTEGER. By making an explicit typecast to FLOAT and adding the suggestion of Quassnoi, it seems to work.

DECLARE @MyTable TABLE (Column1 VARCHAR(50))
INSERT INTO @MyTable VALUES('11.6')
INSERT INTO @MyTable VALUES('-1')
INSERT INTO @MyTable VALUES('1,000')
INSERT INTO @MyTable VALUES('10"    ')
INSERT INTO @MyTable VALUES('Non-Numeric String')

SELECT CASE WHEN ISNUMERIC(REPLACE(Column1,'"','')) = 1 THEN REPLACE(REPLACE(Column1,'"',''), ',', '.') ELSE CAST(0 AS FLOAT) END
FROM @MyTable

Regards,
Lieven

Answer 3

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.

Answer 4

There are many issues with SQL isnumeric. For example:

select isnumeric('1e5')

This will return 1 but in many languages if you try to convert it to a number it will fail. A better approach is to create your own user defined function with the parameters you need to check for:

http://www.tek-tips.com/faqs.cfm?fid=6423