CODEWITHSUNDEEP
sqlsql-serversql-server-2008
StuffHappens
StuffHappens

Reputation: 6557

Error converting data type varchar to float isnumeric = 1

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?

Upvotes: 5

Views: 2731

Answers (3)

Attila
Attila

Reputation: 31

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

Upvotes: 2

A.J. Schroeder
A.J. Schroeder

Reputation: 411

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.

Upvotes: 7

M.Ali
M.Ali

Reputation: 69554

ISNUMERIC() function will return 1 for values like 123e3 because these values are Interpreted as numeric values. Because sql server sees this as 123 , 3 to the powers of 10 which is really a numeric value.

You should try something like....

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

This will return any row where there is a non-numeric character, even values with a ..

Upvotes: 2

Related Questions