Fifteen Things I Hate About ISNUMERIC

Yello!

Thanks for reading!

Brent says: the funny part to me is that if you just try to union all of them, SQL Server throws its hands up:

Result:

COME ON SQL SERVER YOU JUST TOLD ME THEY WERE ALL NUMERIC, dammit, convert to numeric for me if you’re so smart.

Previous Post
Computed Columns and Cardinality Estimates
Next Post
SET IMPLICIT_TRANSACTIONS ON Is One Hell of a Bad Idea

16 Comments. Leave new

  • ISNUMERIC sucks, but TRY_CONVERT is awesome 🙂

    Reply
  • Hah, I have nearly the same demo that use to freak out the Devs when I spot them trying isnumeric!

    I also ran Try_Convert with all the options to show them what a mess that is compared to ISNumeric. Enjoy guessing what works and why! Or sanitize yer darn data in .NET rather than asking SQL to do it!

    DECLARE @Numbs TABLE([Test] VARCHAR(25), [Label] VARCHAR(25) NOT NULL, [Set] VARCHAR(25))
    INSERT INTO @Numbs values (‘.’,’Period’,’Chars’),(‘,’,’Comma’,’Chars’),(‘-‘,’Minus’,’Chars’),(‘+’,’Plus’,’Chars’)
    ,(‘ ‘,’Tab’,’Chars’),(‘ ‘,’Space’,’Chars’),(NULL,’SQLNULL’,’Chars’)
    ,(”,’EmptyString’,’Chars’),(CHAR(0),’Char0′,’Chars’),(‘\’,’Backslash’,’Chars’)
    ,(CHAR(10),’Backspace’,’Chars’),(CHAR(11),’VerticalTab’,’Chars’)
    ,(CHAR(12),’FormFeed’,’Chars’),(CHAR(13),’Newline’,’Chars’)
    ,(‘$’,’Dollar’,’Currency’),(‘£’,’Pound’,’Currency’),(‘€’,’Euro’,’Currency’)
    ,(‘0E0′,’EmbeddedE’,’Notation’),(‘0D0′,’EmbeddedD’,’Notation’)
    ,(‘-0′,’NegZero’,’Mixed’),(‘- 0′,’NegSpaceZero’,’Mixed’),(‘-0e-0′,’NegENeg’,’Mixed’)
    ,(‘-.’,’NegPeriod’,’Mixed’),(‘-.,’,’NegPeriodComma’,’Mixed’),(‘-,.’,’NegCommaPeriod’,’Mixed’)
    ,(‘1111111111111111111111111′,’Long25′,’Long’),(‘11111111111111111111′,’Long20′,’Long’)
    ,(‘111111111111111′,’Long15′,’Long’),(‘1111111111′,’Long10′,’Long’),(‘11111′,’Long5′,’Long’)
    ,(‘333,444.1′,’EmbedComma’,’Separators’),(‘333.444,1′,’EmbedCommaTail’,’Separators’)
    ,(‘333.444.555′,’Embed2Period’,’Separators’),(‘333,444,555′,’Embed2Comma’,’Separators’)
    ,(‘1,234.567E8′,’EmbedCommaNotation’,’Separators’),(‘1,2,3,4,5,6′,’CommaList’,’Separators’)
    ,(‘,1,2,3,4,5,6′,’CommaListPrefix’,’Separators’)

    select [Set], [Label], [Test], CAST([Test] AS VARBINARY(50)) AS [Hex], ISNUMERIC([Test]) AS [Is?]
    , TRY_CONVERT(tinyint,[Test]) AS [TINYINT], TRY_CONVERT(smallint,[Test]) AS [SMALLINT], TRY_CONVERT(int,[Test]) AS [INT]
    , TRY_CONVERT(bigint,[Test]) AS [BIGINT], TRY_CONVERT(decimal,[Test]) AS [DECIMAL], TRY_CONVERT(numeric,[Test]) AS [NUMERIC]
    , TRY_CONVERT(float,[Test]) AS [FLOAT], TRY_CONVERT(real,[Test]) AS [REAL]
    , TRY_CONVERT(smallmoney,[Test]) AS [SMALLMONEY], TRY_CONVERT(money,[Test]) AS [MONEY]
    FROM @Numbs
    ORDER BY [Set], [Label]

    Reply
    • I was going to post just the int,decimal,float version of this and challenge people to guess which ones worked. But seeing your extended counter example list ; wtf on the leading comma vs non-leading comma on the comma separated ints….. anyone who scores more than about 5 points right has spent too much time doing hand coded sql ….

      Reply
    • Henrik Staun Poulsen
      February 28, 2018 1:39 am

      hi Mark,
      Thank you very much for a horrible example of isnumeric and TryConvert. I was not aware that try_convert was that bad.

      Reply
    • That’s a great demo! What’s the bottom line that you give your devs?
      P.S. CHAR(10) is line feed, not backspace

      Reply
  • @Brent ascii() returns an int, although converting to numeric will still generate an error.

    Reply
    • @Brent, I also beleive you were a bit fast on the trigger there, because the example with ISNUMERIC uses “CHAR(9)” where as the next example uses “ASCII(9)”, so they aren’t comparable 🙂
      Anyway love your Posts, keep em comming 😉

      Reply
  • I still have a few clients on 2008 so when testing for positive integers I use [SometimesAnInt] NOT LIKE ‘%[^0-9]%’

    Reply
  • I stumbled upon this (https://stackoverflow.com/questions/4603292/check-if-a-varchar-is-a-number-tsql) a while back regarding issues with ISNUMERIC and a WIP solution…

    Cheers

    Reply
  • Granger Godbold
    March 1, 2018 12:16 pm

    On that note, I still find it a critical oversight that one cannot reliably convert a string directly to a decimal when it’s in scientific notation. Forcing people to go through a float means the number can be corrupted. E.g. SELECT CONVERT(decimal(30, 15), CONVERT(float, ‘0.543e+2’)) —> 54.299999999999997

    Reply
    • Erik Darling
      March 1, 2018 12:20 pm

      Granger — yep, I used to run into this problem a lot when importing Excel files. Never found a more reliable way.

      Reply
  • […] Out comes ISNUMERIC and all its failings. […]

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}