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.
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
Well, ISNUMERIC still stinks, but at least TRY_CONVERT does better.
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!
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.