ISNUMERIC() bug?

  • DK Young

    SSC Enthusiast

    Points: 128

    Hello,  I just encountered a strange result using the ISNUMERIC function...specifically something like SELECT ISNUMERIC('9990D7') or SELECT ISNUMERIC('9D7'). These statements return a 1, meaning it evaluated the value as a numeric value. I noticed this is true for values that start with one or more digits, followed by either a 'D' or an 'E' and followed by one or more digits.

    Can anyone reproduce this? Or has anyone else encountered this?

    Thanks!

    Dan

  • David.Poole

    SSC Guru

    Points: 75109

    I seem to remember that there was a thread about it once before but can't find it now.

    The E is something to do with powers of 10. 1E1 = 10, 1E2 = 100 etc hence the ISNUMERIC thing.

    There was a logical reason for the D as well but I can't remember what it was.

    Ultimately it is not a bug just the single most annoying "feature" I have ever come across.

  • DK Young

    SSC Enthusiast

    Points: 128

    Thanks for the reply and the explanation. The E makes sense, though it would have been nice to have read that in the docs.  It's actually the D that's killing me.  Oh well, I guess I can scan through each position and check for valid numeric that way. Thanks again for the fast response! 

    Dan

  • David.Poole

    SSC Guru

    Points: 75109

    Have you considered using LIKE %[^0-9]% as a comparison?

    In otherwords if your string contains characters in that are not digits?

  • Mark Johnson-176476

    SSCommitted

    Points: 1526

    From Frank Kalis' post on July 20:

    "D and E are used in Fortran to represent FLOATs in scientific notation. SQL Server internally uses C routines for ISNUMERIC that follow these formats. So ISNUMERIC returns 1 for 0d830 and 0e830, because they are evaluated to be convertible to FLOAT.

    The reason why

    select convert(float,'0D830')

    returns 0 is simple. 0d830 is a shortcut to 0*10^830 which obviously is 0 for every INT you place after the d. "

     

  • Vladan

    SSC-Insane

    Points: 21885

    ISNUMERIC is not a good way to find out, whether a string contains only numbers - it is rather a test, whether conversion to number will cause error.

    SELECT ISNUMERIC('+002') --> 1

    SELECT ISNUMERIC('-3E265') --> 1

    SELECT ISNUMERIC('6D78') --> 1

    SELECT ISNUMERIC('6D7897') --> 0

    BTW, not only ISNUMERIC, but also ISNULL can sometimes cause unexpected results thanks to conversion of result datatype. Try this (I have copied it about 1 year ago from one post here, I think it was by Frank Kalis, too.)

    SELECT

     7 / ISNULL(CAST(NULL AS int), 2.00),

     7 / COALESCE(CAST(NULL AS int), 2.00),

     7 / CASE WHEN CAST(NULL AS int) IS NULL THEN 2.00 END

    CREATE TABLE #t(c1 CHAR(2))

    INSERT INTO #t VALUES (NULL)

    --ISNULL provádí konverzi a rídí se puvodní délkou sloupce

    SELECT ISNULL(c1,'Frank'), COALESCE(c1,'Frank')

    FROM  #t

    SELECT  ISNULL(c1,'Frank'), COALESCE(c1,'Frank'), CASE WHEN c1 IS NULL THEN 'Frank' ELSE c1 END

    FROM  #t

    DROP TABLE #t

  • DK Young

    SSC Enthusiast

    Points: 128

    Good point!  Thanks again for the reply.  It is greatly appreciated!

  • DK Young

    SSC Enthusiast

    Points: 128

    Okay, that explains a lot!  Thanks! 

  • sushila

    SSC-Dedicated

    Points: 35293

    same starcast - different movie - this the thread you were looking for...?!?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • detzu

    SSC Journeyman

    Points: 87

    Please explain this one :

     

    12/16/2005 2:49:21 PM:  Ascii code : 0   isnumeric : 0

     12/16/2005 2:49:21 PM:  Ascii code : 1   isnumeric : 0

     12/16/2005 2:49:21 PM:  Ascii code : 2   isnumeric : 0

     12/16/2005 2:49:21 PM:  Ascii code : 3   isnumeric : 0

     12/16/2005 2:49:21 PM:  Ascii code : 4   isnumeric : 0

     12/16/2005 2:49:21 PM:  Ascii code : 5   isnumeric : 0

     12/16/2005 2:49:21 PM:  Ascii code : 6   isnumeric : 0

     12/16/2005 2:49:21 PM:  Ascii code : 7   isnumeric : 0

     12/16/2005 2:49:21 PM:  Ascii code : 8   isnumeric : 0

     12/16/2005 2:49:21 PM:  Ascii code : 9   isnumeric : 1

     12/16/2005 2:49:21 PM:  Ascii code : 10   isnumeric : 1

     12/16/2005 2:49:21 PM:  Ascii code : 11   isnumeric : 1

     12/16/2005 2:49:21 PM:  Ascii code : 12   isnumeric : 1

     12/16/2005 2:49:21 PM:  Ascii code : 13   isnumeric : 1

     12/16/2005 2:49:21 PM:  Ascii code : 14   isnumeric : 0

     12/16/2005 2:49:21 PM:  Ascii code : 15   isnumeric : 0

     12/16/2005 2:49:21 PM:  Ascii code : 16   isnumeric : 0

     12/16/2005 2:49:21 PM:  Ascii code : 17   isnumeric : 0

     12/16/2005 2:49:21 PM:  Ascii code : 18   isnumeric : 0

     12/16/2005 2:49:21 PM:  Ascii code : 19   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 20   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 21   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 22   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 23   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 24   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 25   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 26   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 27   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 28   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 29   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 30   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 31   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 32   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 33   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 34   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 35   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 36   isnumeric : 1

     12/16/2005 2:49:22 PM:  Ascii code : 37   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 38   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 39   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 40   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 41   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 42   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 43   isnumeric : 1

     12/16/2005 2:49:22 PM:  Ascii code : 44   isnumeric : 1

     12/16/2005 2:49:22 PM:  Ascii code : 45   isnumeric : 1

     12/16/2005 2:49:22 PM:  Ascii code : 46   isnumeric : 1

     12/16/2005 2:49:22 PM:  Ascii code : 47   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 48   isnumeric : 1

     12/16/2005 2:49:22 PM:  Ascii code : 49   isnumeric : 1

     12/16/2005 2:49:22 PM:  Ascii code : 50   isnumeric : 1

     12/16/2005 2:49:22 PM:  Ascii code : 51   isnumeric : 1

     12/16/2005 2:49:22 PM:  Ascii code : 52   isnumeric : 1

     12/16/2005 2:49:22 PM:  Ascii code : 53   isnumeric : 1

     12/16/2005 2:49:22 PM:  Ascii code : 54   isnumeric : 1

     12/16/2005 2:49:22 PM:  Ascii code : 55   isnumeric : 1

     12/16/2005 2:49:22 PM:  Ascii code : 56   isnumeric : 1

     12/16/2005 2:49:22 PM:  Ascii code : 57   isnumeric : 1

     12/16/2005 2:49:22 PM:  Ascii code : 58   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 59   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 60   isnumeric : 0

     12/16/2005 2:49:22 PM:  Ascii code : 61   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 62   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 63   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 64   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 65   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 66   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 67   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 68   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 69   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 70   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 71   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 72   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 73   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 74   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 75   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 76   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 77   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 78   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 79   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 80   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 81   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 82   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 83   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 84   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 85   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 86   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 87   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 88   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 89   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 90   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 91   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 92   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 93   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 94   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 95   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 96   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 97   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 98   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 99   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 100   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 101   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 102   isnumeric : 0

     12/16/2005 2:49:23 PM:  Ascii code : 103   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 104   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 105   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 106   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 107   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 108   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 109   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 110   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 111   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 112   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 113   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 114   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 115   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 116   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 117   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 118   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 119   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 120   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 121   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 122   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 123   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 124   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 125   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 126   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 127   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 128   isnumeric : 1

     12/16/2005 2:49:24 PM:  Ascii code : 129   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 130   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 131   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 132   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 133   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 134   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 135   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 136   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 137   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 138   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 139   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 140   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 141   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 142   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 143   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 144   isnumeric : 0

     12/16/2005 2:49:24 PM:  Ascii code : 145   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 146   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 147   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 148   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 149   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 150   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 151   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 152   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 153   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 154   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 155   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 156   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 157   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 158   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 159   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 160   isnumeric : 1

     12/16/2005 2:49:25 PM:  Ascii code : 161   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 162   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 163   isnumeric : 1

     12/16/2005 2:49:25 PM:  Ascii code : 164   isnumeric : 1

     12/16/2005 2:49:25 PM:  Ascii code : 165   isnumeric : 1

     12/16/2005 2:49:25 PM:  Ascii code : 166   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 167   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 168   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 169   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 170   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 171   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 172   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 173   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 174   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 175   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 176   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 177   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 178   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 179   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 180   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 181   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 182   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 183   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 184   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 185   isnumeric : 0

     12/16/2005 2:49:25 PM:  Ascii code : 186   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 187   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 188   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 189   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 190   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 191   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 192   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 193   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 194   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 195   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 196   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 197   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 198   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 199   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 200   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 201   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 202   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 203   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 204   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 205   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 206   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 207   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 208   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 209   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 210   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 211   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 212   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 213   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 214   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 215   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 216   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 217   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 218   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 219   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 220   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 221   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 222   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 223   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 224   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 225   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 226   isnumeric : 0

     12/16/2005 2:49:26 PM:  Ascii code : 227   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 228   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 229   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 230   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 231   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 232   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 233   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 234   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 235   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 236   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 237   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 238   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 239   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 240   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 241   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 242   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 243   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 244   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 245   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 246   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 247   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 248   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 249   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 250   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 251   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 252   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 253   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 254   isnumeric : 0

     12/16/2005 2:49:27 PM:  Ascii code : 255   isnumeric : 0

     

    the script to make this output :

    declare @i integer

    set @i = 0

    while @i < 256

    begin

     print N'Ascii code : ' + CAST ( @i as varchar) + '   isnumeric : ' + cast (isnumeric(char(@i)) as varchar) 

     set @i = @i + 1

    end

  • Jeff Moden

    SSC Guru

    Points: 994239

    Sure, one complete explanation coming right up...  but I'm a bit surprised you didn't figure it out yourself... remember, you asked the question...

    First, I modified your code so we could see the actual character being represented instead of just the ASCII numeric representation....

    declare @i integer

    set @i = 0

    while @i < 256

    begin

     print N'Ascii code : ' + CAST ( @i as varchar) +' '+ CHAR(@I)

       + '   isnumeric : ' + cast (isnumeric(char(@i)) as varchar)

     set @i = @i + 1

    end

    Here's the result set which has been abbreviated as a readability-courtesy to show only those single characters that are considered to be numeric...

    Ascii code : 9     isnumeric : 1

    Ascii code : 10

       isnumeric : 1

    Ascii code : 11    isnumeric : 1

    Ascii code : 12    isnumeric : 1

    Ascii code : 13

       isnumeric : 1

    Ascii code : 36 $   isnumeric : 1

    Ascii code : 43 +   isnumeric : 1

    Ascii code : 44 ,   isnumeric : 1

    Ascii code : 45 -   isnumeric : 1

    Ascii code : 46 .   isnumeric : 1

    Ascii code : 48 0   isnumeric : 1

    Ascii code : 49 1   isnumeric : 1

    Ascii code : 50 2   isnumeric : 1

    Ascii code : 51 3   isnumeric : 1

    Ascii code : 52 4   isnumeric : 1

    Ascii code : 53 5   isnumeric : 1

    Ascii code : 54 6   isnumeric : 1

    Ascii code : 55 7   isnumeric : 1

    Ascii code : 56 8   isnumeric : 1

    Ascii code : 57 9   isnumeric : 1

    Ascii code : 128 €   isnumeric : 1

    Ascii code : 160     isnumeric : 1

    Ascii code : 163 £   isnumeric : 1

    Ascii code : 164 ¤   isnumeric : 1

    Ascii code : 165 ¥   isnumeric : 1

    Ascii 9 is a TAB character and is included because a column of numbers is frequently delimited by a TAB.

    Ascii 10 is a Line Feed character and is included because the last column of numbers is frequently terminated by a Line Feed character.

    Ascii 11 is a Vertical Tab character and is included because the last column of numbers is frequently terminated by a Vertical Tab character.

    Ascii 12 is a Form Feed character and is included because the last column numbers of the last row is sometimes terminated by a Form Feed character.

    Ascii 13 is a Carriage Return character and is included because the last column of numbers is frequently terminated by a Carriage Return character.

    Ascii 36 (Dollar sign), 128 (Euro sign), 163 (British Pound sign), and 164 (Yen sign) are included because they are frequently used as enumerators to identify the type of number or, in this case, the currency type the number is meant to represent.

    Ascii 43 (Plus sign), 44 (Comma), 45 (Minus sign), and 46 (Decimal place) are included because they are frequently included in numeric columns to mark where on the number line the number appears and for simple formatting.

    Ascii 160 is a special "hard space" and is included because it is frequently used to left pad numeric columns so the column of numbers appears to be right justified.  Ascii 32 is a "soft space" and is not included because a single space does not usually represent a column of numbers.  Ascii 32 is, however, a valid numeric character when used to create right justified numbers as is Ascii 160 but a single Ascii 32 character is NOT numeric.  In fact, a string of Ascii 32 spaces is not considered to be numeric but a string of spaces with even a single digit in it is considered to be numeric.

    Ascii 164 is a special character and is included because it is frequently used by accountants and some software to indicate a total or subtotal of some type.  It is also used by some to indicate they don't know what the enumerator is.

    Ascii 48-59 are included because they represent the digits 0 through 9.

    Do notice that the "e" , "d" and (everybody forgot about this) "x" are not included as numeric in the results because a single "e", "d", or "x"  is NOT considered to be numeric.  Two of these letters are for two different forms of scientific notation and the "x" indicates a hexidecimal output in SQL.  Further, when properly formatted, the letters "a-f" are also considered to be numeric and are also not included in the list of single characters.  All of these "unlisted" characters are highly positional in nature.  That is, they must be in the correct position in association with numeric digits and other numeric symbology to be considered to be numeric.  Depending on where they appear in a column of numbers determines if and when they will be treated as numeric or not.

    ISNUMERIC is NOT and should never be treated as ISALLDIGITS.  It was never intended to mean that.  Use the NOT LIKE '%[^0-9]%' regular expression to determine if something ISALLDIGITS... If you try to use ISNUMERIC for that, then you're using it the wrong way.

    Any more questions on ISNUMERIC?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • detzu

    SSC Journeyman

    Points: 87

    Thank you for the work ! It's really helpfull .

     

    Why can not select cast ( '123'+char(160) as int) work ?

     

    Seems to be a difference of coding between this 2 functions. ISNUMERIC working differenty as cast is not a good idea.

    Again thank you for the detailed explanation.

  • Frank Kalis

    SSC Guru

    Points: 111183

    You might want to check out my reply here: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=202581 CHAR(160) is a non-breakable space and as such considered a "noise" character in SQL Server. At least CAST treats it this way. ISNUMERIC however treats this character as thousands separator.

    ISNUMERIC is probably one of the most useless functions in SQL Server, IMHO. ....at least, when you don't need to rely on this weired behaviour.

    Jeff, can you give an example where 'a-f' are considered by ISNUMERIC to be convertable to a number?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Vladan

    SSC-Insane

    Points: 21885

    Interesting thing though... when you CAST/CONVERT to INT, CHAR(160) will raise an error. But if you try CAST/CONVERT to FLOAT, statement will succeed and the nbsp character is ignored, if it is at the beginning of string (not if it is on the right end of string).

    select cast(char(160)+'1234' as float)

    select convert(float, char(160)+'1234')

    Probably, as Jeff said, it is ignored if used to pad the numbers from left. Well... At the same time, select ISNUMERIC(char(160) + '1234') returns 0. As mentioned already several times, ISNUMERIC is rather unpredictable and should be avoided or used with great care : some strings that return ISNUMERIC = 0 can be successfully converted to float.

  • Don Bryan-238042

    Valued Member

    Points: 67

    modify the script to examine the "numeric" characters
     

    declare

    @i integer

    set

    @i = 0

    while

    @i < 256

    begin

    if isnumeric(char(@i)) = 1

    print N'Ascii code : ' + CAST ( @i as varchar) + ' isnumeric : ' + cast (isnumeric(char(@i)) as varchar) + ' Char : ' + char(@i)

    set @i = @i + 1

    end

    result

    Ascii code : 9 isnumeric : 1 Char :
    Ascii code : 10 isnumeric : 1 Char :
    Ascii code : 11 isnumeric : 1 Char :
    Ascii code : 12 isnumeric : 1 Char :
    Ascii code : 13 isnumeric : 1 Char :
    Ascii code : 36 isnumeric : 1 Char : $
    Ascii code : 43 isnumeric : 1 Char : +
    Ascii code : 44 isnumeric : 1 Char : ,
    Ascii code : 45 isnumeric : 1 Char : -
    Ascii code : 46 isnumeric : 1 Char : .
    Ascii code : 48 isnumeric : 1 Char : 0
    Ascii code : 49 isnumeric : 1 Char : 1
    Ascii code : 50 isnumeric : 1 Char : 2
    Ascii code : 51 isnumeric : 1 Char : 3
    Ascii code : 52 isnumeric : 1 Char : 4
    Ascii code : 53 isnumeric : 1 Char : 5
    Ascii code : 54 isnumeric : 1 Char : 6
    Ascii code : 55 isnumeric : 1 Char : 7
    Ascii code : 56 isnumeric : 1 Char : 8
    Ascii code : 57 isnumeric : 1 Char : 9
    Ascii code : 92 isnumeric : 1 Char : \
    Ascii code : 128 isnumeric : 1 Char : €
    Ascii code : 160 isnumeric : 1 Char :  
    Ascii code : 162 isnumeric : 1 Char : ¢
    Ascii code : 163 isnumeric : 1 Char : £
    Ascii code : 164 isnumeric : 1 Char : ¤
    Ascii code : 165 isnumeric : 1 Char : ¥

    For the most part the explanation is "valid" punctuation in a numeric "sting". Currency, signs and separators. Can't explain Horizontal Tab(9), Newline(10), Vertical Tab(11), Form Feed (12) and carriage Return (13) other than their presence at the beginning of a numeric "string" does allow the sting to evaluate as numeric.

    select

    isnumeric(char(13)+'123' )Characters above 127 will be different based on the codepage currently loaded.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply