CODEWITHSUNDEEP
sqlsql-server-2008
Rabin
Rabin

Reputation: 418

SQL IsNumeric function

SELECT IsNumeric('472369326D4')

is returning 1. Clearly, there is a aphabet D in the string. Why ?

Upvotes: 2

Views: 119

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270823

You probably want logic like this:

(case when str not like '%[^0-9]%' then 1 else 0 end) as isNumeric

Or, if you want to allow decimals and negative signs the logic is a little more cumbersome:

(case when str not like '%.%.%' and str not like '%[^.0-9]%' and
           str not like '-%[^.0-9]%'
      then 1 else 0
 end)

I strongly recommend not using isnumeric(). It produces strange results. In addition to 'd', 'e' is allowed.

In SQL Server 2012+, you can try the following:

select x, isnumeric(x), try_convert(float, x)
from (values ('$'), ('$.'), ('-'), ('.')) v(x)

All of these return 1 for isnumeric, but NULL for the conversion, meaning that you cannot convert the value to a float. (You can run the code in SQL Server 2008 with convert() instead and watch the code fail.)

Upvotes: 0

Igor
Igor

Reputation: 62288

472369326D4 is a valid float type. The D4 is translated as adding four 0 values, effectively multiplying the value before the D character by 10000.

Example Sql

SELECT cast('472369326D4' as float) 
SELECT cast('472369326D3' as float) 
SELECT cast('472369326D2' as float)

Output: 

4723693260000
472369326000
47236932600

Upvotes: 10

Related Questions