Hüseyin Durmuş Hüseyin Durmuş - 2 months ago 7
SQL Question

ISNUMERIC function with Tab char(9)

I was working with ISNUMERIC function in SQL Server, when I encountered a strange trouble.

Check text including below characters ISNUMERIC function return 1(true). Why?

Tab char(9)
Line feed char(10)
Carriage return char(13)


Thanks

Answer

Not only Char(9),it does for all of the values below..

SELECT [Ascii Code]        = STR(Number),
        [Ascii Character]   = CHAR(Number),
        [ISNUMERIC Returns] = ISNUMERIC(CHAR(Number))
   FROM Master.dbo.spt_Values
  WHERE Type = 'P'
    AND Number BETWEEN 0 AND 255
    AND ISNUMERIC(CHAR(Number)) = 1

Ascii Code Ascii Character ISNUMERIC Returns 
---------- --------------- ----------------- 
         9                 1
        10                 1
        11                 1
        12                 1
        13                 1
        36 $               1
        43 +               1
        44 ,               1
        45 -               1
        46 .               1
        48 0               1
        49 1               1
        50 2               1
        51 3               1
        52 4               1
        53 5               1
        54 6               1

From the Official docs..emphasis on highlighted..

ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types

So Isnumeric will return 1 if it returns any one of the type in highlighted part above..

Jeff moden Describes the why part here..

There are many different combinations of letters, numbers, and symbols that can actually be converted to numeric data types and ISNUMERIC will return a "1" for all of them. It's not a flaw... that's the way it's supposed to work!

References:
http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/

Comments