Don’t use ISNUMERIC: Use TRY_CONVERT or a CASE Expression

Kid Anymore


A while back I blogged about how ISNUMERIC can give you some wacky results.

Working with a client… Well, it’s hard to know based on my publishing schedule. But they were using ISNUMERIC all over the place, and getting the same wacky results, and even errors.

Here’s a short setup:

SELECT
    x.n
INTO #t
FROM
(
    SELECT '$'AS n
    UNION ALL 
    SELECT ','AS n
    UNION ALL
    SELECT '.'AS n
    UNION ALL
    SELECT ',1,'AS n
    UNION ALL
    SELECT '-'AS n
    UNION ALL
    SELECT '+'AS n
    UNION ALL
    SELECT CHAR(9)AS n 
    UNION ALL
    SELECT CHAR(10)AS n 
    UNION ALL
    SELECT CHAR(11)AS n
    UNION ALL
    SELECT CHAR(12)AS n 
    UNION ALL
    SELECT CHAR(13)AS n
) AS x;

We’re definitely not dumping anything in that table that’s a number.

Competitors


Let’s see what happens when we run this query:

SELECT
    t.n,
    i = ISNUMERIC(t.n),
    t = TRY_CONVERT(bigint, t.n),
    c = CASE 
            WHEN t.n NOT LIKE '%[^0-9]%'
            THEN 1
            ELSE 0
        END
FROM #t AS t

The first column is what each of the next three columns are evaluating:

oops

You can see ISNUMERIC stink it up every time. TRY_CONVERT mostly does okay, but I’m not sure how I feel about + and – being converted to zero.

That case expression is the only thing that seems reliable.

Of course, integers are stodgy. Stiff. Maybe we need something a little more floaty.

SELECT
    t.n,
    i = ISNUMERIC(t.n),
    t = TRY_CONVERT(float, t.n),
    c = CASE 
            WHEN t.n NOT LIKE '%[^0-9]%'
            THEN 1
            ELSE 0
        END
FROM #t AS t

What happens?

more right

Well, ISNUMERIC still stinks, but at least TRY_CONVERT does better.

Majority


No one should be using ISNUMERIC, period. Depending on your query needs, you can certainly use TRY_CONVERT in most situations, assuming you’re on SQL Server 2016+.

For everyone else, and even maybe people on 2016+, that CASE expression works really well for rooting out things that aren’t numbers.

Thanks for reading!



4 thoughts on “Don’t use ISNUMERIC: Use TRY_CONVERT or a CASE Expression

  1. ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). For a complete list of currency symbols, see money and smallmoney (Transact-SQL).
    https://docs.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql?view=sql-server-ver15

    Would it be useful for MS to create a new function ISMONEY (ISMONETARY) and keep ISNUMERIC pure … is it a number or is it not a number, that is the only question being asked!

Leave a Reply

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