SELECT IsNumeric('472369326D4')
is returning 1. Clearly, there is a aphabet D in the string. Why ?
-
This was some interested related reading: dba.stackexchange.com/questions/76834/…digital.aaron– digital.aaron2018-08-22 19:24:44 +00:00Commented Aug 22, 2018 at 19:24
-
Possible duplicate of SQL Server's ISNUMERIC functionSam M– Sam M2018-08-22 19:26:33 +00:00Commented Aug 22, 2018 at 19:26
-
Also see Why is ISNUMERIC(',') true?GSerg– GSerg2018-08-22 19:48:03 +00:00Commented Aug 22, 2018 at 19:48
Add a comment
|
2 Answers
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
1 Comment
Rabin
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')
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.)
