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

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:

SQL Server Query Results
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?

SQL Server Query Results
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!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.



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

  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!

Comments are closed.