SQL IsNumeric function

Question

SELECT IsNumeric('472369326D4')

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

This was some interested related reading: https://dba.stackexchange.com/questions/76834/what-is-the-logic-behind-isnumeric-for-certain-special-characters — digital.aaron, Aug 22 '18 at 19:24
Possible duplicate of [SQL Server's ISNUMERIC function](https://stackoverflow.com/questions/32135878/sql-servers-isnumeric-function) — Sam M, Aug 22 '18 at 19:26
Also see [Why is ISNUMERIC(',') true?](https://stackoverflow.com/q/24053613/11683) — GSerg, Aug 22 '18 at 19:48

Igor · Accepted Answer · 2018-08-22T19:29:53.743

10

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

edited Aug 22 '18 at 19:29

answered Aug 22 '18 at 19:22

Igor

60,821
10
100
175

Hmm.. Thats very interesting. I will accept your answer. I was checking if this is Numeric and converting to BigInt which was failing . Something like SELECT CONVERT(bigInt , '472369326D4') – Rabin Aug 23 '18 at 13:51

Gordon Linoff · Answer 2 · 2018-08-22T19:42:19.203

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.)

SQL IsNumeric function

2 Answers2