locked
isnumeric('0') = 0, value is a zero but returns 0, verified with ascii(), replace the value and =1 WHY? RRS feed

  • Question

  • I have a table with a bunch of values that I a need to validate before I load.  There are a bunch of zeros, I have verified this by select ascii(col) from table where index = 1

    when I run isnumeric I get 0, when I update the value with a 0 I get 1, so what value is in this field that looks like zero, smells like zero, walks like zero but isn't zero.


    me

    Friday, February 12, 2016 1:52 AM

Answers

  • You probably have some non-printable characters (like space or tab etc).  Ascii() only returns the ASCII value of the first character.  But the problem may be beyond that character.  Cast the value as varbinary to see all characters.  To see what I mean, try running

    Declare @d varchar(2);
    Set @d = '0 '
    Select @d
    Select ASCII(@d)
    Select ISNUMERIC(@d)
    Select CAST(@d As varbinary(128))
    Tom

    • Proposed as answer by Eric__Zhang Friday, February 12, 2016 4:36 AM
    • Marked as answer by john15nlt Friday, February 12, 2016 4:47 AM
    Friday, February 12, 2016 3:48 AM

All replies

  • Can you post some sample date with your table DDL?

    Select isnumeric('0') 

    returns 1.

    Friday, February 12, 2016 2:06 AM
  • Well, ASCII('0') returns 79, so you may have some non printable characters instead.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, February 12, 2016 3:35 AM
  • You probably have some non-printable characters (like space or tab etc).  Ascii() only returns the ASCII value of the first character.  But the problem may be beyond that character.  Cast the value as varbinary to see all characters.  To see what I mean, try running

    Declare @d varchar(2);
    Set @d = '0 '
    Select @d
    Select ASCII(@d)
    Select ISNUMERIC(@d)
    Select CAST(@d As varbinary(128))
    Tom

    • Proposed as answer by Eric__Zhang Friday, February 12, 2016 4:36 AM
    • Marked as answer by john15nlt Friday, February 12, 2016 4:47 AM
    Friday, February 12, 2016 3:48 AM
  • It is capital letter 'O' that has Ascii value 79.  The digit '0' has Ascii value 48.  But I agree with you, the OP probably has some nonprintable character(s) in the data.

    Tom

    Friday, February 12, 2016 3:51 AM
  • Oops, it's a bit late here and I somehow used O instead of 0 in my test.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, February 12, 2016 3:52 AM
  • SELECT DISTINCT ingcost, isnumeric(ingCost), ASCII(ingCost)  FROM pd_claim_seg4
    				


    me

    Friday, February 12, 2016 4:45 AM
  • SELECT DISTINCT ingcost, isnumeric(ingCost), ASCII(ingCost) ,  CAST(ingcost As varbinary(128)) FROM pd_claim_seg4
    				


    me

    Friday, February 12, 2016 4:48 AM
  • Thank you very much.

    me

    Friday, February 12, 2016 4:49 AM