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.

Solution 1

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.

Solution 2

First convert the string to money, then covert it to any other numeric format since money type gives a true numeric string always. You will never see an error then.

Try the following in your query, and you'll know what I am talking about. Both will return 2345.5656. The Money datatype is rounded to 4 decimal places, and hence the casting causes rounding to 4 decimal places.

SELECT CAST('2,345.56556' as money), CAST('$2,345.56556' as money)

Cast( cast('2,344' as money) as float) will work perfectly or cast( cast('2,344' as money) as decimal(7,2)) will also work.

Even cast(CAST('$2,345.56556' as money) as int ) will work perfectly rounding it to nearest integer.

Solution 3

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

Solution 4

IsNumeric(' ') also returns 1, but then CAST as int blows up. Brendan above says write your own function. He is correct.

Solution 5

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.

View more solutions

Comments

[Kyle B.]

I see that, but when trying to cast value as float I get the same problem.

[Kyle B.]

This worked. I think I was looking for something too elaborate to cover this scenario. Thanks.

[Kyle B.]

No, I removed the comma entirely.

[Quassnoi]

In some locales (mine, for instance), "1,000" is 1. A "thousand" will be "1 000,00".

[samir105]

How to do with FormView bound element?

[Quassnoi]

@samir105: how to do what? what's a FormView? what's a bound element?

[samir105]

I meant ASP.NET WebForms FormView data binding. Sorry, It works with no problem in SqlDataSource. Thanks

[sparebytes]

On my SQL 2008 machine, IsNumeric(' ') returns 0.

[Joel Coehoorn]

Better still: don't store number fields in string columns in the first place.