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!