I am using SQL Server 2008 R2. When I execute the following query, ISNUMERIC is evaluating to true(1) when the barcode clearly has 'D' inside it.
SELECT ISNUMERIC('7210300106D30')
If I execute the same code with other letter than D or E, it seems to evaluate to false(0) which is what I expect.
SELECT ISNUMERIC('7210300106K30')
Can anyone please shed a light as to why this may be happening? Thanks.
Edit
My initial answer is wrong, it is because e is used in scientific notation to assign an exponent, and D is used aparently to to notate a number format too.
like 10e3
Do notice that "e" and "d" (everybody forgets about this) are not included as numeric in the results because a single "e" or "d is NOT considered to be numeric. HOWEVER, these letters represent two different forms of numeric notation (the one with the "e" is Scientific Notation). So, if you have anything that looks like the following, ISNUMERIC will identify them as "Numeric"...
SELECT ISNUMERIC('0d2345') SELECT ISNUMERIC('12e34')
http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/
It might see it as a hexadecimal number, which is anything within the number sequence:
0123456789ABCDEF
Hexadecimal describes a base-16 number system. That is, it describes a numbering system containing 16 sequential numbers as base units (including 0) before adding a new position for the next number. (Note that we're using "16" here as a decimal number to explain a number that would be "10" in hexadecimal.) The hexadecimal numbers are 0-9 and then use the letters A-F.
http://whatis.techtarget.com/definition/hexadecimal
D with A, B, or C you will get 0.If you are looking for strings of digits, don't use isnumeric(). Just use like:
select (case when col like '%[^0-9]%' then 0 else 1 end) as IsAllDigits
If you want to try a conversion, then use try_convert():
select (case when try_convert(bigint, col) is null then 0 else 1 end) as IsConvertable
Looks like @Tschallacka's answer is correct. The input is treated as a hexadecimal number.
If it is not expected, you can operate in following way:
SELECT ISNUMERIC(RTRIM('7210300106D30') + '.')
5, numeric for our purposes? According to this trick, yes. What about -$? Why of course, that's a money type. If all you want is to exclude letters, then this will do, but usually you just want to avoid ISNUMERIC altogether.I would use TRY_CAST (... as bigint)
ISNUMERIC is known to be flawed with a dot, a space, the letter E, and several others
SELECT TRY_CAST('7210300106D30' AS bigint)
Gives NULL, so it is not numeric
Using LIKE and negatives
IF '7210300106D30' LIKE '%[^0-9]%'
PRINT 'broke'
ELSE
PRINT 'OK'
IF '143618726378451623' LIKE '%[^0-9]%'
PRINT 'broke'
ELSE
PRINT 'OK'
12345678901 clearly is numeric, yet will fail this conversion.
- and . and ,, of course.)Try this
Here the TRY_CONVERT is try to convert the value into bigint format
If the given value is not pure number then the TRY_CONVERT return null
By using 'case' we return unconvertable value as 0 and convertable values as 1
SELECT
CASE WHEN TRY_CONVERT(bigint , '7210300106D30') IS NULL
THEN 0
ELSE 1
END AS Value;
7210300106030420000 fails (doesn't fit in a DECIMAL), 721030010603042.0000 succeeds (even though it contains a period).
Dis accepted byISNUMERICandCONVERTis probably a holdover from FORTRAN, as that's the only language that does anything with this notation, as far as I can tell. This isn't even consistent in SQL Server itself:1e3is a literal with value1000, but1d3is parsed as the constant1aliased to the column named3.ISNUMERICis the gift that keeps on giving.