Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

What is wrong with IsNumeric()?

May 19 2012 12:00AM by Kirti M.Darji   

T-SQL's ISNUMERIC() function has a problem. It can falsely interpret non-numeric letters and symbols (such as D, E, and £), and even tabs (CHAR(9)) as numeric.

Read More..   [296 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Kirti M.Darji
9 · 16% · 5006
10
 
3
 
 
 
0
Interesting
 
0
Forgotten



Submit

11  Comments  

  • commented on May 19 2012 1:33AM
    Viral Sarvaiya
    176 · 1% · 279
  • Actually, this is a bit wrong. ISNUMERIC does exactly what it was designed to do. It does NOT "falsely" interpret letters and symbols. It returns a "1" if the data can be converted to ANY numeric datatype. That includes things that have "white-space" like tabs in them. It includes certain letters like "E" and "D" for different forms of scientific notation. It includes certain punctuation like commas and decimal points. It includes signs like "+", '-', currency symbols, and parenthesis for certain forms of negative numbers. Etc, etc.

    The REAL problem with ISNUMERIC is what people "think" ISNUMERIC should mean. It certainly DOESN'T mean "IsAllDigits" or "IsADecimalNumber" or "IsAnInteger". It simply means that the character can be converted to some form of numeric datatype even if it's not the datatype you're trying to check for.

    For more information, please see the following article. http://www.sqlservercentral.com/articles/IsNumeric/71512/

    commented on May 19 2012 8:13AM
    Jeff Moden
    161 · 1% · 300
  • If you want to test for integers, try IsNumeric(@stringvalue + '.0E0')

    commented on May 19 2012 7:04PM
    ErikEckhardt
    65 · 3% · 898
  • Two excellent writeups on that subject:
    A CLR Alternative To The SQL Server ISNUMERIC Function:
    http://visualstudiomagazine.com/articles/2011/10/11/a-clr-alternative-to-isnumeric.aspx:

    Why doesn't ISNUMERIC work correctly?:
    http://www.sqlservercentral.com/articles/IsNumeric/71512/

    commented on May 30 2012 10:12AM
    Dave Vroman
    130 · 1% · 393
  • A CLR Alternative To The SQL Server ISNUMERIC Function:

    I'd like to express a word of caution here...

    I realize that VB and C# are both compiled languages that can be made to run at near machine language speeds. The problem is that calling CLRs does have some overhead and not everyone writes good VB or C#. CLR is not the performance panacea that so many make it out to be.

    On the flip side, there are a large number of places where SQL Server will also run at near machine language speeds. Just because it's an "interpreted" language, doesn't mean that it can't use objects that have been compiled to run at near machine language speeds.

    To wit and quite contrary to urban legend, there are a lot of places where doing something in T-SQL will actually blow the doors off a CLR equivalent. Just because something is written as a CLR doesn't mean the CLR will automatically win the proverbial performance or resource footrace.

    Even if a CLR does end up beating T-SQL in one fashion or another, you might also want to ask yourself if it's going to be worth maintaining code in two places. For example if T-SQL takes 2 seconds to do something to a million rows and the CLR takes 1 second to do the same thing to those million rows, is the CLR worth it? Of course, Only you and your team can answer that.

    My suggestion is that before you implement any CLR in production, that you do performance, resource, scalability testing, and a little checking on reality. Depending on what you're doing, you may have a huge surprise and sudden appreciation for what T-SQL can actually do for you. Usually with T-SQL, all you have to do is not suck real bad at using it. As with all else in SQL Server, "It Depends" ;-)

    commented on May 30 2012 7:11PM
    Jeff Moden
    161 · 1% · 300
  • If you want to test for integers, try IsNumeric(@stringvalue + '.0E0')

    That's some very clever and simple looking code. Let's try it and compare it to a more traditional method. {EDIT} Just to be clear. Eric's code does a test for integers... mine only does a test for "IsAllDigits" which also happens to work for unsigned postive integers. There is such a thing as negative integers. I apologize for mistakenly saying mine worked for integers especially since I made no such claim in the article that links were previously provided for.

    --===== Conditionally drop the test table to make reruns easier in SSMS
         IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
            DROP TABLE #TestTable
    ;
    --===== Create and build the test table on-the-fly.
     SELECT TOP 1000000
            SomeString = SUBSTRING(CAST(NEWID() AS CHAR(36)),15,4)
       INTO #TestTable
       FROM sys.all_columns ac1,
            sys.all_columns ac2
    ;
    GO
    --===== Test the traditional method for finding strings containing only digits
        SET STATISTICS TIME OFF;
      PRINT '========== Somestring NOT LIKE ''%[^0-9]%'' ============================';
    DECLARE @BitBucket INT;
        SET STATISTICS TIME ON;
     SELECT COUNT(*)
       FROM #TestTable 
      WHERE Somestring NOT LIKE '%[^0-9]%'
    ;
    GO
    --===== Test the "try this" method for finding integers.  This IS different than finding just all digits.
        SET STATISTICS TIME OFF;
      PRINT '========== ISNUMERIC(SomeString+''.0E0'') ================================';
    DECLARE @BitBucket INT;
        SET STATISTICS TIME ON;
     SELECT COUNT(*) 
       FROM #TestTable 
      WHERE ISNUMERIC(SomeString + '.0E0') = 1
    ;
    GO
    SET STATISTICS TIME OFF;
    

    Here are the results on my 10 year old desk top. Your results will be different but I believe you'll find that the traditional method always beats the second because string concatenation is fairly expensive. {EDIT} If you're looking for "IsAllDigits", then you might want to use the first code. If you're actually looking for INTEGERS which can contain negative numbers, then Eric's code (the second code) is the easier and faster way to go.

    (1000000 row(s) affected)
    ========== Somestring NOT LIKE '%[^0-9]%' ============================
    
    (1 row(s) affected)
    
    SQL Server Execution Times:
       CPU time = 1250 ms,  elapsed time = 1460 ms.
    SQL Server parse and compile time: 
       CPU time = 0 ms, elapsed time = 0 ms.
    ========== ISNUMERIC(SomeString+'.0E0') ================================
    
    (1 row(s) affected)
    
    SQL Server Execution Times:
       CPU time = 1922 ms,  elapsed time = 2305 ms.
    SQL Server parse and compile time: 
       CPU time = 0 ms, elapsed time = 0 ms.
    
    commented on May 30 2012 7:42PM
    Jeff Moden
    161 · 1% · 300
  • In fact, now that I've provided a test gig, someone else run the test and include a CLR function in a similar manner. Let's find out if the CLR would be faster in this case.

    commented on May 30 2012 8:00PM
    Jeff Moden
    161 · 1% · 300
  • @Jeff,

    The idea about appending a tail with isnumeric is a general one that works for more situations than just integers—such as testing for decimals (@string + 'e0').

    But more importantly, '-1' and '$1' are integers, too. What performance do you get if you accommodate those using pattern matching?

    If you don't care about these, then of course just checking for only digits is fine. There may be additional non-digit decorations on numbers that do not prevent them from being classified as integers.

    commented on May 31 2012 3:36PM
    ErikEckhardt
    65 · 3% · 898
  • Crud... thanks for the catch, Erik. I apologize for saying that mine was for "integers" because I certainly didn't mean that. It's not. Mine was only for "IsAllDigits" and by the time this post clears, I'll hope to have corrected my previous post. I don't even come close to making such a claim in the article someone provided a link for. Not sure why I said "Integer" because it sure isn't.

    commented on May 31 2012 6:25PM
    Jeff Moden
    161 · 1% · 300
  • Actually, to my amusement mine is not for integers either as '$1e0' is not considered numeric. But '-1e0' is.

    The lesson? Know your tools!

    commented on May 31 2012 6:39PM
    ErikEckhardt
    65 · 3% · 898
  • heh... that's alright. I don't consider $1 to be an integer. It's a whole dollar which is currency. I know... semantics, but, yes, I consider your code to be good for determining if something is an integer or not.

    commented on Jun 1 2012 7:14AM
    Jeff Moden
    161 · 1% · 300

Your Comment


Sign Up or Login to post a comment.

"What is wrong with IsNumeric()?" rated 5 out of 5 by 10 readers
What is wrong with IsNumeric()? , 5.0 out of 5 based on 10 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]