SQL SERVER – ISNUMERIC Function and Interesting Result

Today’s blog post is based on a very interesting observation sent to my client of the Comprehensive Database Performance Health Check. While I primarily focus on improving the performance of SQL Server, often I receive question-related to T-SQL and I enjoy answering them. Today we will talk about the ISNUMERIC Function and Interesting Result.

SQL SERVER - ISNUMERIC Function and Interesting Result isnumericfunction-800x657

As you know ISNUMERIC function can be used to determine if a given expression is a valid numeric. However, my client faced a very interesting behavior of this function. Let me recreate the same example.

Consider the following example

DECLARE @NUMBER VARCHAR(10)
SET @NUMBER='12.45'
SELECT ISNUMERIC(@NUMBER) AS IS_NUMERIC

The result is, as expected, 1

Now execute the following code and see the result

DECLARE @NUMBER VARCHAR(10)
SET @NUMBER='12e5'
SELECT ISNUMERIC(@NUMBER) AS IS_NUMERIC

The result is 1 although the input value contains the string e which is not a valid numeric value. ISNUMERIC function evaluates the expression written in scientific notation to be true and that’s why the result is 1.

Well, I explained this to my client and suggested that in this situation they can either re-write their own function or before they pass the value to IS_NUMERIC, they need to check if there are any other alphanumeric values are included. If we attempt to do that, that actually is what we expect from this function and it seems like we are talking about catch 22 situations.

As this is a new year, let me remind you how my consulting work actually works. Here are a few blog posts which I had written earlier.

If you want to prevent this, what is the change you make in the ISNUMERIC function to return the value as 0 for the expression 12e5?

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Function, SQL Scripts, SQL Server, SQL String
Previous Post
SQL SERVER – Parameter Sniffing and OPTION (RECOMPILE)
Next Post
SQL SERVER 2019 – How to Enable Lock Pages in Memory LPIM?

Related Posts

7 Comments. Leave new

  • Hi Pinal,

    Interesting case! I found that ISNUMERIC returns 1 for ‘+’ , ‘-‘ , ‘$’ and ‘.’

    DECLARE @NUMBER VARCHAR(10)
    SET @NUMBER=’$’
    SELECT ISNUMERIC(@NUMBER) AS IS_NUMERIC

    Reply
  • Declare @Temp Table(NumericData VarChar(max))

    Insert Into @Temp Values(NULL)
    Insert Into @Temp Values(‘1’)
    Insert Into @Temp Values(‘2’)
    Insert Into @Temp Values(‘1e4’)

    Solution 1
    SELECT CAST(NumericData AS bigint)
    FROM @Temp
    WHERE IsNumeric(NumericData + ‘.0e0’) = 1

    Solution 2
    Select PatIndex(‘%[^0-9]%’, NumericData)
    FROM @Temp
    If anything above 0 is returned we know that this has some characters in it other then 0 to 9

    Reply
  • Albert Van Biljon
    January 2, 2020 1:28 pm

    ISNUMERIC is quite broad. It probably depends quite a bit then on what you want to achieve as to how to create a function to do it. The most obvious need for me would be an ISNUMERIC-function.
    For ease of creating it, I might start with just calling ISNUMERIC and if it evaluates to 1, then have a further test whether it is actually an integer. Performance-wise, one would have to do further testing to see what works best.

    Reply
  • Change the alphabet ‘e’ to any other value and then test again.

    Reply
  • I had a similar situation come up when working with a customer recently. When tweaking some of their report queries to accommodate the new year 2020, we discovered that isnumeric() was returning true if there was a comma in the string. I found an old blog about this behavior and learned that the try_parse() function is an effective way of working around the problem.

    Here’s the blog (https://blogs.msdn.microsoft.com/manub22/2013/12/23/use-new-try_parse-instead-of-isnumeric-sql-server-2012/).

    Reply
  • I have the .e0e trick running on my ETL. Came to the comments to share it. Glad you got there before me.

    Reply
  • Anthariksh Bhargav
    January 3, 2020 4:06 pm

    We can do a TRY_PARSE and apply IS_NUMERIC function on top of it.

    SELECT ISNUMERIC(TRY_PARSE(’12e3′ as int)) as ‘IS_Numeric’

    Reply

Leave a Reply