Error converting data type varchar to float isnumeric = 1

Question

When I run the script:

select 
    cast(s as float)
from 
    t
where 
    ISNUMERIC(s) = 1

it stops with the error:

Error converting data type varchar to float.

Why does it happen? I'm trying to convert to float only numerics. How do I found out which row causes the error?

Answer 1

The isnumeric function thinks just about everything is a number. Use "try_convert" instead. if the value can't convert to your destination datatype, it returns null.

select convert(float, '1,0,1')
where try_convert(float, '1,0,1') is not null

If you are on an older version of SQL, I would write my own function.

Answer 2

I usually face with this when the value in a column you are trying to convert to float contains a comma (,) as thousand separator:

SELECT ISNUMERIC('140,523.86')

The Result is 1, but unable to cast it as a float. By replacing it works fine for me:

SELECT
   CAST( replace(s,',','') AS float ) AS Result
FROM t
WHERE ISNUMERIC(replace(s,',','')) = 1