What does IsNumeric do ?

Question

ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0 (MSDN reference).

So why is the output of this query 1?

select ISNUMERIC('5d9')

Answer 1

Hexa : 5d9 <==> Decimal : 1497

Answer 2

Not sure bit taking a shot at it!! :-)

ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type

I think that in this case, it is considering the "d" to stand for decimal and hence considering it to be a number.

I think 5e9 should be similar as well as "e" is also used mathematically to denote exponent..Dont have SQL here to try it out right now..but i think result of 5e9 should be 1 for the same reason

EDIT : Some more context here (result of some quick googling!!)

Answer 3

select cast('5d9' as float)

Returns

5000000000

d appears to work in the same manner as e

Answer 4

ISNUMERIC() has no practical use and should generally be avoided.

It tells you if a string can be converted to a numeric data type but, as you saw, that can include seemingly random characters. eg. it can also include +, -, $, etc.

(obviously, they're not random, but if you're using ISNUMERIC() for validation, you'll struggle to find a case where you want to allow all possibilities it allows)