Today my friend asked me that ISNUMERIC function is not working properly. I checked, the issue was with some special char, might be MSSQL will improve this functionality in coming versions. For now, if we change the way of calling, it will show you the correct result.
These conditions shows wrong result:
SELECT ISNUMERIC(CHAR(9)) -- TAB CHAR
SELECT ISNUMERIC('-1,2,3,4,56,789') --NON MONEY
SELECT ISNUMERIC('12222D3') --NON NUMERIC WITH ALPHA CHAR
Results:
-----------
1
(1 row(s) affected)
-----------
1
(1 row(s) affected)
-----------
1
(1 row(s) affected)
The solution is:
SELECT ISNUMERIC(CHAR(9)+'E0')
SELECT ISNUMERIC('-1,2,3,4,56,789'+'E0')
SELECT ISNUMERIC('12222D3' + 'E0')
Result:
-----------
0
(1 row(s) affected)
-----------
0
(1 row(s) affected)
-----------
0
(1 row(s) affected)
MSDN:
"Note this still doesn't guarantee you will be able to convert the
number to a float without the second ISNUMERIC(value + 'e0') test
included to weed out values where there are more than one period or
multiple hyphens."