Error converting varchar data type to float isnumeric = 1

Question

When I run the script:

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

it stops with an error:

Error converting varchar data type to float.

Why is this happening? I am trying to convert only numeric values. How to find out which line is causing the error?

Answer 1

The isnumeric function thinks all of this is a number. Use try_convert instead. if the value cannot be converted to your destination data type, it returns null.

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

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

Answer 2

, , float, (,) :

SELECT ISNUMERIC('140,523.86')

1, float. , :

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

Answer 3

ISNUMERIC() 1 123e3, . sql- 123 , 3 to the powers of 10, .

- ....

Select * 
From tableName 
WHERE Col NOT LIKE '%[^0-9]%'

, , ..