Advertisements
Home > SQL Server Questions > Bug with ISNUMERIC() – MSDN TSQL forum

Bug with ISNUMERIC() – MSDN TSQL forum


–> 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'

 

–> My Answer:

Yes, “12e45” is still numeric, notice the small ‘e’ which is a symbol for exponent for representing a big number.

But yes, NUMERIC() function does results incorrect results for some values, 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.

After release of SQL Server 2012 and ahead you must be using TRY_PARSE() instead of ISNUMERIC().

Check my blog post on how to use this – Use new TRY_PARSE() instead of ISNUMERIC() | SQL Server 2012
 

–> Answer by CELKO:

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.

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.
 

–> Answer by Erland Sommarskog:

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

 

–> Answer by Dan Guzman:

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

 

Ref link.


Advertisements
  1. Mita
    March 7, 2016 at 8:43 pm

    12e45 = 1.2e46 (= 1.2E+46) – Exponential notation of a number

    • March 8, 2016 at 10:55 am

      thanks @Mita, for your comments and pointing this out.
      You are right, and I’ve update the blog post !

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: