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

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.