Why ISNumeric() Transact-SQL function treats some var-chars as Int

Question

SELECT some_column 
FROM some_table 
WHERE some_column = '3.'

Return row/rows

SELECT some_column 
FROM some_table 
WHERE ISNUMERIC(some_column) = 0 
  AND some_column IS NOT NULL 
  AND some_column <> ''

Does not return any non numeric row/rows. there is a row which has a column value of '3.'

Am i missing something. Please Advise.

Can you provide an example on https://dbfiddle.uk/ or http://sqlfiddle.com/? — Shawn, Nov 29 '18 at 21:21
Is this `[sql-server]`? (Please tag accordingly) And which version? (Please tag accordingly). `ISNUMERIC` is quite ancient and well known for some rather weird values. There are better approaches... And read [mcve]. Please try to set up a *stand-alone* sample with test data and expected output. — Shnugo, Nov 29 '18 at 21:21
Considering you *hopefully* are using SQL Server 2012+ (if you are still using 2008 you're very close to running completely out of support) I suggest using `TRY_CONVERT` or `TRY_CAST`. For example, instead of `ISNUMERIC('.') = 1` use `TRY_CONVERT(int, '.') IS NOT NULL`. — Thom A, Nov 29 '18 at 21:34
If you are still on a version below 2012, [this answer](https://stackoverflow.com/a/53279357/5089204) provides a good workaround to simulate `TRY_CAST()` using a XML-hack. — Shnugo, Nov 29 '18 at 21:50
[`IsNumeric()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql) is notoriously [problematic](http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/). — HABO, Nov 30 '18 at 03:47

Gordon Linoff · Answer 1 · 2018-11-29T21:37:08.040

2

I don't understand the question. ISNUMERIC('3.') returns 1. So it would not be returned by the second query. And, presumably, no other rows would either.

Perhaps you really intend: somecolumn not like '%[^0-9]%'. This will guarantee that somecolumn has only the digits from 0-9.

In SQL Server 2012+, you can also use try_convert(int, somecolumn) is not null.

edited Nov 29 '18 at 21:37

answered Nov 29 '18 at 21:20

Gordon Linoff

1,242,037
58
646
786

Thanks for the reply. So the table has more than 50K rows and the column I'm interested in should only accept INT value but somehow a few non-integer values ended up in the column which now causing the issue in Data Mapping. I'm trying to comb through the column to get all non-numeric values. ISNumeric(some_column) = 0 or ISNumeric(some_column) <> 1 should return desired result. – Mubeen Nov 29 '18 at 21:34

score 1 · Accepted Answer · answered Nov 29 '18 at 21:26

1

ISNUMERIC() has some flaws. It can return True/1 for values that are clearly not numbers.

SELECT 
ISNUMERIC('.'), 
ISNUMERIC('$')

If this is causing you issues, try using TRY_PARSE()

SELECT 
TRY_PARSE('2' AS INT)

You can use this and filter for non-null results.

SELECT some_column 
FROM some_table 
WHERE TRY_PARSE(some_column AS INT) IS NOT NULL

answered Nov 29 '18 at 21:26

dfundako

8,022
3
18
34

Thanks for the reply. I'm working on 2008 SP1 which apparently doesnt support this function. smh – Mubeen Nov 29 '18 at 21:38
Good answer. Note, however that SELECT TRY_PARSE('3.' AS INT) will return 3 which means this solution might not work. `TRY_CAST` or `TRY_CONVERT` might be better perhaps? – Alan Burstein Nov 29 '18 at 21:49

score 0 · Answer 3 · answered Nov 29 '18 at 21:56

ISNUMERIC is a legacy function, I would don't like it personally. It does exactly what it's supposed to do which is usually not what you need ("Is Numeric" is a very subjective question in Computer Science.) I recently wrote this query to clarify things for some co-workers:

SELECT string = x, [isnumeric says...] = ISNUMERIC(x) 
FROM (VALUES ('1,2,3,,'),(',,,,,,,,,,'),(N'﹩'),(N'＄'),(N'￠'),('12,0'),(N'52,3,1.25'),
             (N'-4,1'),(N'56.'),(N'5D105'),('1E1'),('\4'),(''),(N'\'),(N'₤'),(N'€')) x(x);

Returns:

string     isnumeric says...
---------- -----------------
1,2,3,,    1
,,,,,,,,,, 1
﹩         0
＄         1
￠         0
12,0       1
52,3,1.25  1
-4,1       1
56.        1
5D105      1
1E1        1
\4         1
           0
\          1
₤          1
€          1

TRY_CAST or TRY_CONVERT, or WHERE somecolumn not like '%[^0-9]%' as Gordon said, could be a good alternative.

For performance reasons it might not be a bad idea to pre-aggregate, persist and index the column by adding a new computed column. E.g. something like

ALTER <your table> 
ADD isGoodNumber AS (ABS(SIGN(PATINDEX('%[^0-9]%',<your column>))-1)

This would return a 1 for rows only containing digits or a 0 otherwise. You can then index isGoodNumber (you pick a better name) for better performance.

Why ISNumeric() Transact-SQL function treats some var-chars as Int

3 Answers3