Professional Web Applications Themes

IsNumeric Bug? - Microsoft SQL / MS SQL Server

Hello All, I have a problem with the IsNumeric function. I am trying to convert the values in a varchar column to float only when they are numeric, so I do the test using IsNumeric. From the BOL: "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." In my case, one row in the table contains a dot: select isnumeric( '.' ) returns 1, so I would expect that ...

  1. #1

    Default IsNumeric Bug?

    Hello All,

    I have a problem with the IsNumeric function. I am trying to convert the
    values in a varchar column to float only when they are numeric, so I do the
    test using IsNumeric.

    From the BOL:
    "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."

    In my case, one row in the table contains a dot:

    select isnumeric( '.' ) returns 1, so I would expect that SQL Server is able
    to convert that to float, but:

    select convert(float,'.'), or

    select cast( '.' as float )


    they both generate an error "Error converting data type varchar to float."

    Is there any other method to detect wheter an expression is numeric or not,
    or do I need to write my own function?

    Thanks a lot,

    Mike






    Mike Sarbu Guest

  2. #2

    Default Re: IsNumeric Bug?

    Here is one called isReallyNumeric, but as pointed out earlier, it should
    really be called isReallyInteger (see the bottom of
    [url]http://www.aspfaq.com/2390[/url] )




    "Mike Sarbu" <m_sarbu> wrote in message
    news:uNPJjIXQDHA.3664tk2msftngp13.phx.gbl...
    > Hello All,
    >
    > I have a problem with the IsNumeric function. I am trying to convert the
    > values in a varchar column to float only when they are numeric, so I do
    the
    > test using IsNumeric.
    >
    > From the BOL:
    > "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."
    >
    > In my case, one row in the table contains a dot:
    >
    > select isnumeric( '.' ) returns 1, so I would expect that SQL Server is
    able
    > to convert that to float, but:
    >
    > select convert(float,'.'), or
    >
    > select cast( '.' as float )
    >
    >
    > they both generate an error "Error converting data type varchar to float."
    >
    > Is there any other method to detect wheter an expression is numeric or
    not,
    > or do I need to write my own function?
    >
    > Thanks a lot,
    >
    > Mike
    >
    >
    >
    >
    >
    >

    Aaron Bertrand - MVP Guest

  3. #3

    Default Re: IsNumeric Bug?

    Bug?
    \/
    one of
    \/
    at least one of
    \/
    not necessarily all of
    \/
    Why bother investigating
    whenever one can argue with the customer.
    Furthermore, who cares if the customer cannot
    conviently do something customers often need to do?

    Bye,
    Delbert Glass

    "Mike Sarbu" <m_sarbu> wrote in message
    news:uNPJjIXQDHA.3664tk2msftngp13.phx.gbl...
    > Hello All,
    >
    > I have a problem with the IsNumeric function. I am trying to convert the
    > values in a varchar column to float only when they are numeric, so I do
    the
    > test using IsNumeric.
    >
    > From the BOL:
    > "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."
    >
    > In my case, one row in the table contains a dot:
    >
    > select isnumeric( '.' ) returns 1, so I would expect that SQL Server is
    able
    > to convert that to float, but:
    >
    > select convert(float,'.'), or
    >
    > select cast( '.' as float )
    >
    >
    > they both generate an error "Error converting data type varchar to float."
    >
    > Is there any other method to detect wheter an expression is numeric or
    not,
    > or do I need to write my own function?
    >
    > Thanks a lot,
    >
    > Mike
    >
    >
    >
    >
    >
    >

    Delbert Glass Guest

  4. #4

    Default Re: IsNumeric Bug?

    Addendum: I updated this article with two functions: isReallyInteger, and
    isReallyNumeric which really finds decimal and whole numbers.




    > [url]http://www.aspfaq.com/2390[/url]

    Aaron Bertrand - MVP Guest

  5. #5

    Default Re: IsNumeric Bug?

    Thanks a lot Aaron,

    It doesn't work for negative numbers, though.

    select dbo.isreallynumeric( '-3' ) returns 0.

    As for my problem, I discovered that actually convert(money,'.') works,
    while convert(float,'.') does not work. Any idea why '.' makes sense as
    a money and not as a float?

    Thanks everybody,

    Mike



    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Mike Sarbu Guest

  6. #6

    Default Re: IsNumeric Bug?

    BOL is correct but you need to be aware of the "or" in
    "valid integer,floating point number, money or decimal type"

    A work around is to first cast as money and then cast as the target data
    type.

    select cast( cast( '.' as money) as float)

    which returns
    MyFloat
    -----------------------------------------------------
    0.0

    Here is another example with spaces and the dollar sign that is numeric
    (it will cast to money) but cannot be directly cast to float.

    select cast( cast( ' $99.1234 ' as money) as float) as MyFloat

    Carl Federl
    Please post DDL (create table) with datatypes, primary and foreign keys.

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Carl Federl Guest

  7. #7

    Default Re: IsNumeric Bug?

    You're right, good catch, I wasn't even thinking about the negative sign.
    See if the functions (just updated at that URL) work better now...





    "Mike Sarbu" <anonymousdevdex.com> wrote in message
    news:Ow7E$PZQDHA.1988TK2MSFTNGP12.phx.gbl...
    > Thanks a lot Aaron,
    >
    > It doesn't work for negative numbers, though.
    >
    > select dbo.isreallynumeric( '-3' ) returns 0.
    >
    > As for my problem, I discovered that actually convert(money,'.') works,
    > while convert(float,'.') does not work. Any idea why '.' makes sense as
    > a money and not as a float?
    >
    > Thanks everybody,
    >
    > Mike
    >
    >
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Aaron Bertrand [MVP] 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

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