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)
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
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
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.
Change the alphabet ‘e’ to any other value and then test again.
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/).
I have the .e0e trick running on my ETL. Came to the comments to share it. Glad you got there before me.
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’