• srikanth_pallerla (8/7/2009)


    why isnumeric gives true when the content contains ','. May be question seems simple but i am new to sql server.

    As per the Books Online page (http://msdn.microsoft.com/en-us/library/ms186272.aspx):

    "ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0."

    Valid numeric data types include things like Money data types - "ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see Using Monetary Data."

    Integer is a pretty specific data type. It won't accept things that are considered Numeric in some cases. If you need the ability to detect whether something is an Integer, I'd suggest writing your own UDF. There's many different ways to do it. One thing you can do is to cast the value to a VARCHAR data type, then loop through every character, and see if every character evaluates to an ASCII value between 48 and 57 (numbers 0-9); if any don't,then you'll know that it's not an Integer.