none
Bug with ISNUMERIC() RRS feed

  • Question

  • Has anyone seen this SQL bug before? 

    If you use the IsNumeric function with 12345 and replace number 3 with an "e" and no other letter, SQL still thinks it's numeric. 

    if ISNUMERIC('12e45') = 1
    print 'Is Numeric'
    else 
    Print 'No'

    Wednesday, July 8, 2015 1:00 AM

Answers

  • Hi BerzinP,

    As per the BOL:ISNUMERIC, 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 money and smallmoney (Transact-SQL).

    SELECT 
     ISNUMERIC('123') as '123'
     ,ISNUMERIC('abc') as 'abc'
     ,ISNUMERIC('-') as '-'
     ,ISNUMERIC('+') as '+'
     ,ISNUMERIC('$') as '$'
     ,ISNUMERIC('.') as '.'
     ,ISNUMERIC(',') as ','
     ,ISNUMERIC('\') as '\'

    See a blog Use new TRY_PARSE() instead of ISNUMERIC() | SQL Server 2012

    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support


    Thursday, July 9, 2015 7:01 AM
    Moderator
  • ISNUMERIC isn't particularly useful for the common use case of checking for a string containing only the digits 0 through 9.   Consider a CASE expression instead to get the desired behavior:

    CASE WHEN @value LIKE '%[^0-9]%' OR @value = '' THEN 0 ELSE 1 END


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, July 8, 2015 3:18 AM
  • In addition to other posts, on SQL2012 or later, you can use try_convert to check if the number is convertible to a specific data type:

      SELECT CASE WHEN try_convert(int, @str) IS NOT NULL
                  THEN 'Converts'
                  ELSE 'Not convertible'
              END


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 8, 2015 10:28 AM

All replies

  • That isn't a bug, it's just how that function works (yes you could argue this makes it less than useful in a lot of scenarios)

    Consider

    select cast('12e45' as float)

    https://msdn.microsoft.com/en-us/library/ms186272.aspx?f=255&MSPPError=-2147217396
    Wednesday, July 8, 2015 2:01 AM
  • The use of E or e for floating point numbers started in the 1950's with FORTRAN and later in Algol. You wrote "twelve times ten to the forty-fifth power" in the standard notation. The only difference is that some languages have to start with a digit and some can start with the E. 

    <removes the signature, which include advertising, as part of CELKO's abuse treatment>
    Wednesday, July 8, 2015 2:26 AM
  • Hi,

    In addition to disssss's response: It is important to understand that ISNUMERIC does not check if this is number! but if this value can be CONVERTED to numeric. or in other word: Determines whether an expression is a valid numeric type. Therefore, (1) there are several types of numerics like decimal, float, real, int, bigint, smallint, tinyint, money, smallmoney... some of these have several formats that we can use (2) even if the value is not number but can be converted to numeric according the internal set of implicit convert rules, then it will return 1.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]




    Wednesday, July 8, 2015 2:44 AM
    Moderator
  • ISNUMERIC isn't particularly useful for the common use case of checking for a string containing only the digits 0 through 9.   Consider a CASE expression instead to get the desired behavior:

    CASE WHEN @value LIKE '%[^0-9]%' OR @value = '' THEN 0 ELSE 1 END


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Wednesday, July 8, 2015 3:18 AM
  • In addition to other posts, on SQL2012 or later, you can use try_convert to check if the number is convertible to a specific data type:

      SELECT CASE WHEN try_convert(int, @str) IS NOT NULL
                  THEN 'Converts'
                  ELSE 'Not convertible'
              END


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 8, 2015 10:28 AM
  • Hi BerzinP,

    As per the BOL:ISNUMERIC, 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 money and smallmoney (Transact-SQL).

    SELECT 
     ISNUMERIC('123') as '123'
     ,ISNUMERIC('abc') as 'abc'
     ,ISNUMERIC('-') as '-'
     ,ISNUMERIC('+') as '+'
     ,ISNUMERIC('$') as '$'
     ,ISNUMERIC('.') as '.'
     ,ISNUMERIC(',') as ','
     ,ISNUMERIC('\') as '\'

    See a blog Use new TRY_PARSE() instead of ISNUMERIC() | SQL Server 2012

    If you have any question, feel free to let me know.


    Eric Zhang
    TechNet Community Support


    Thursday, July 9, 2015 7:01 AM
    Moderator
  • After release of SQL Server 2012 and ahead you must be using TRY_PARSE() instead of ISNUMERIC().

    Check here on how to use this: http://sqlwithmanoj.com/2013/09/20/use-new-try_parse-instead-of-isnumeric-sql-server-2012/

    As others mentioned ISNUMERIC() won't give to correct results in some cases, like:

    SELECT
         ISNUMERIC('123') as '123'
        ,ISNUMERIC('.') as '.' --Period
        ,ISNUMERIC(',') as ',' --Comma
    
    SELECT
         ISNUMERIC('123') as '123'
        ,ISNUMERIC('-') as '-'
        ,ISNUMERIC('+') as '+'
        ,ISNUMERIC('$') as '$'
        ,ISNUMERIC('\') as '\'

    gives you 1 for all these non-numeric values.


    ~manoj | email: http://scr.im/m22g
    http://SQLwithManoj.com
    MCCA 2011 | My FB Page



    Friday, March 4, 2016 1:43 PM
  • Please look up how floating numbers are displayed. This has been true for over 60 years! The E or e is for "exponent" in the notation that goes back to FORTRAN I. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, March 4, 2016 4:52 PM