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:

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?

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 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.