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.

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.

• Consulting 101 – Why Do I Never Take Control of Computers Remotely?
• Consulting 102 – Why Do I Give 100% Guarantee of My Services?
• Consulting 103 – Why Do I Assure SQL Server Performance Optimization in 4 Hours?
• Consulting 104 – Why Do I Give All of the Performance-Tuning Scripts to My Customers?
• Consulting 105 – Why Don’t I Want My Customers to Return Because of the Same Problem?
• Consulting Wrap Up – What Next and How to Get Started

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)