Professional Web Applications Themes

ISNUMERIC bug SQL2000 sp3 ?? - Microsoft SQL / MS SQL Server

Is there a bug with ISNUMERIC? If I run this script (SQL2000, sp3) I get a true (1) response when it should be false (0). Here is an example. Neither are valid numerics, but for the first, I get a return value of 1. -- this returns 1, improperly telling me it is numeric DECLARE mystring varchar(20), isnumericvalue int SET mystring = '3d8' print 'mystring ' + mystring SELECT isnumericvalue = ISNUMERIC(mystring) print cast(isnumericvalue as varchar(20)) go -- this returns 0, properly telling me it is not numeric DECLARE mystring varchar(20), isnumericvalue int SET mystring = '3b8' print 'mystring ' ...

  1. #1

    Default ISNUMERIC bug SQL2000 sp3 ??

    Is there a bug with ISNUMERIC?

    If I run this script (SQL2000, sp3) I get a true (1) response when it should
    be false (0).

    Here is an example. Neither are valid numerics, but for the first, I get a
    return value of 1.

    -- this returns 1, improperly telling me it is numeric

    DECLARE mystring varchar(20), isnumericvalue int
    SET mystring = '3d8'
    print 'mystring ' + mystring
    SELECT isnumericvalue = ISNUMERIC(mystring)
    print cast(isnumericvalue as varchar(20))
    go

    -- this returns 0, properly telling me it is not numeric

    DECLARE mystring varchar(20), isnumericvalue int
    SET mystring = '3b8'
    print 'mystring ' + mystring
    SELECT isnumericvalue = ISNUMERIC(mystring)
    print cast(isnumericvalue as varchar(20))
    go




    Andrew Guest

  2. #2

    Default Re: ISNUMERIC bug SQL2000 sp3 ??

    Hi Andrew,
    That's a little querk of isNumeric.
    Try this function (it's not mine but I forget who to give credit to):
    CREATE FUNCTION dbo.IsReallyNumeric
    (num VARCHAR(19))
    RETURNS BIT
    BEGIN
    RETURN CASE
    WHEN LEFT(num,1) LIKE '[-0-9+.]'
    AND PATINDEX('%[^0-9.]%', SUBSTRING(num, 2, 18)) = 0
    AND LEN(num) - LEN(REPLACE(num, '.', '')) <=1
    THEN
    1
    ELSE
    0
    END
    END

    "Andrew Peterson" <com> wrote in message
    news:phx.gbl... 
    should 



    raydan Guest

  3. #3

    Default Re: ISNUMERIC bug SQL2000 sp3 ??

    So I give credit to you. :-)

    "Jacco Schalkwijk" <co.uk> wrote in message
    news:%phx.gbl... 


    raydan Guest

  4. #4

    Default Re: ISNUMERIC bug SQL2000 sp3 ??

    Thanks

    "Delbert Glass" <com> wrote in message
    news:phx.gbl... [/ref]
    integer, 
    > these numeric types.
    >
    > where that last line of is particular interest because:
    > 'one of'
    > \/
    > at least one of
    > \/
    > not necessarily all of
    >
    > Furthermore BOL says: 
    >
    > Needless to say, things can be confusing consider
    > we have 'numeric types' and one of the numeric types is called 'numeric'.
    > Likewise, the function's name, ISNUMERIC, only adds to the confusion.
    >
    > Give this a try:
    >
    > DECLARE mystring varchar(20), isnumericvalue int
    > SET mystring = '3d8'
    > print 'mystring ' + mystring
    > SELECT isnumericvalue = ISNUMERIC(mystring)
    > print cast(isnumericvalue as varchar(20))
    > print cast(mystring as float) -- works
    > -- print cast(mystring as numeric) -- will fail
    >
    > Notice per the definition that ISNUMERIC does return a proper value.
    > '3d8' is convertable to at least one of the 'numeric types'
    > but not necessarily the 'numeric type' 'numeric'
    > as you were improperly expecting.
    >
    > How that you have climb out of that trap,
    > you can go on to ponder:
    >
    > Is it proper/improper for the convertion
    > of '3d8' to the 'numeric type' 'numeric' to fail?
    > Consider: 300000000
    >
    > Furthermore ponder:
    > Should ISNUMERIC
    > indicate what conversions should/should not fail
    > vs
    > indicate what conversions actually will/will fail
    > (regardless of whether or not the conversion should/shouldn't fail).
    >
    > Don't forget to consider how and why ISNUMERIC is utilized.
    >
    > Don't forget to account for 'one of'
    > (if you improperly (this time) try pondering with your computer ;-) ).
    >
    > Bye,
    > Delbert Glass
    >
    > "Andrew Peterson" <com> wrote in message
    > news:phx.gbl... 
    > should [/ref]
    get 
    >
    >[/ref]


    Andrew Guest

Similar Threads

  1. Replies: 2
    Last Post: July 6th, 01:35 AM
  2. isNumeric in Query - Help
    By edhusar in forum Macromedia ColdFusion
    Replies: 3
    Last Post: April 20th, 05:14 PM
  3. isNumeric Problem
    By hakim in forum Coldfusion - Getting Started
    Replies: 4
    Last Post: March 4th, 02:04 PM
  4. CF, SQL2000, and XML
    By Osgyth in forum Coldfusion Database Access
    Replies: 0
    Last Post: March 3rd, 08:59 PM
  5. IsNumeric Bug?
    By Mike Sarbu in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 3rd, 10:21 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139