Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Apr 2004
    Posts
    5

    Question Unanswered: Bug in ISNUMERIC ?

    strange thing I just ran into, not sure if this is a bug or what ... but pretty annoying.
    In MS SQLServer 2000 :

    SELECT (ISNUMERIC('0E010101'))

    returns "1"

    but

    SELECT CAST ('0E010101' AS numeric)

    returns "Error converting data type varchar to numeric"

    any idea?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ISNUMERIC tests whether a value can be converted to a number of any type.

    CAST ('0E010101' AS numeric) attempts to specifically convert the string to the SQL NUMERIC data type.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    I think he was addressing the fact that there was an 'E' in the numeric string.

    Hypothesis:
    the E is recognized as the exponent value from a float.
    so it includes it.

    I dont know but it sounds good to me.

  4. #4
    Join Date
    Apr 2004
    Posts
    5
    No, same happens with D.
    I thought it could be Hexadecimal, but it doesn't seem to work with ABCFGH
    ???


    Originally posted by Ruprect
    Hypothesis:
    the E is recognized as the exponent value from a float.
    so it includes it.

    Could Be

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Originally posted by Ruprect
    I think he was addressing the fact that there was an 'E' in the numeric string.

    Hypothesis:
    the E is recognized as the exponent value from a float.
    so it includes it.

    I dont know but it sounds good to me.
    I tried the following statement and it gives a 0 so i am going with my Hypothesis
    SELECT (ISNUMERIC('0A010101'))

  6. #6
    Join Date
    Apr 2004
    Posts
    5
    try SELECT (ISNUMERIC('0D010101'))

    Originally posted by Ruprect
    I tried the following statement and it gives a 0 so i am going with my Hypothesis
    SELECT (ISNUMERIC('0A010101'))

  7. #7
    Join Date
    Apr 2004
    Posts
    5
    The strange thing is that SELECT (ISNUMERIC('0DE010101')) returns 0
    but
    SELECT (ISNUMERIC('0D010101'))
    or
    SELECT (ISNUMERIC('0E010101'))
    return 1

    Originally posted by Tontxu
    try SELECT (ISNUMERIC('0D010101'))

  8. #8
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Books Online ISNUMERIC
    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.
    i even tried different combiantions and added an e to the end it appears that it's reading the values with on char in the second position
    no i need to create a script that trys a brute force on every combination

    nahhh
    Last edited by Ruprect; 04-14-04 at 03:07.

  9. #9
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    I don't know much about number but I think Ruprect is right on this one....

    I don't know what that D stands for either,.. but apparently it stands for something....

  10. #10
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    i retracted my d statement
    i thought that it could have been indicative of some type of money
    danish kroners or whatever they buy their crap with.

  11. #11
    Join Date
    Apr 2004
    Posts
    5
    the only thing I know is that it's not only the second position as my initial problem was with
    SELECT (ISNUMERIC('0102D121'))


    my problem was :

    DECLARE @Temp nvachar(25)

    SELECT @Temp = '0000D121'
    SELECT
    CASE
    WHEN ISNUMERIC (@Temp) THEN CONVERT(nvarchar(20),convert(numeric,@Temp))
    ELSE @Temp
    END

    the problem is that @Temp is gonna be a colum from a table so I can predict the values and I'm trying to remove the leading '0'


    Originally posted by rokslide
    I don't know much about number but I think Ruprect is right on this one....

    I don't know what that D stands for either,.. but apparently it stands for something....

  12. #12
    Join Date
    Feb 2004
    Posts
    88
    there is a bug with converting to numeric.

    try:

    select convert(float,1.7256e+06)
    select convert(float,'1.7256e+06')
    select convert(numeric(38,19),1.7256e+06)
    select convert(numeric(38,19),'1.7256e+06')

    in query analyzer...

    Bill

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by rokslide
    I don't know much about number but I think Ruprect is right on this one....

    I don't know what that D stands for either,.. but apparently it stands for something....
    Great zot! No old C programmers around anymore, or what ?!?!

    E is the exponent introducer for real (float*4, 7 digit precision) values. D is the introducer for double (float*8, 53 digit precision) values. M is a suffix for (packed) decimal, and even IsNumeric() fails to process M correctly.

    -PatP

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A history lesson!

    Do old C programmers ever die? Or does their legacy go on?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Damn...learned something new again....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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