Interesting ISNUMERIC Function Results in SQL and its Alternatives

Written by

Introduction

The Questions are, what is ISNUMERIC Function in SQL server and what are those interesting result set behaviors that it has and what are the other alternatives of it.

ISNUMERIC Function in SQL Server

The ISNUMERIC function in SQL Server validates and tells whether an expression is a valid numeric type. And if the value is Numeric, then it will return 1. Or else, it will return 0.

Interesting Results of ISNUMERIC Function

SQL Script

DECLARE @Test_Table AS TABLE (Test_Column VARCHAR(10));
INSERT INTO @Test_Table
SELECT '11' UNION ALL
SELECT '1.0' UNION ALL
SELECT '+5' UNION ALL
SELECT '-5' UNION ALL
SELECT '1 1' UNION ALL
SELECT '0d123' UNION ALL
SELECT '-4.55E-66' UNION ALL
SELECT '+4.55E+66' UNION ALL
SELECT '$50' UNION ALL
SELECT '-' UNION ALL
SELECT '+' UNION ALL
SELECT '.' UNION ALL
SELECT '$' UNION ALL
SELECT '\' UNION ALL
SELECT '2D2' UNION ALL
SELECT ',1,,1' UNION ALL
SELECT ',1,' UNION ALL
SELECT '1,' UNION ALL
SELECT '5.0e4' UNION ALL
SELECT '2e3' UNION ALL
SELECT NULL UNION ALL
SELECT '';

SELECT 
	Test_Column AS Input,
	ISNUMERIC(Test_Column) AS 'ISNUMERIC'
FROM @Test_Table;

Output Result sets


Output Result sets

The SQL ISNUMERIC Function says that the string might be a valid number in many occasions that are not actually a valid number. So, we can’t able to ensure ISNUMERIC functionality to all our all Scenarios.

What are Other Alternatives!

  • TRY_PARSE
  • TRY_CAST
  • Case With Predicate
  • .e0 Trick

SQL Script

DECLARE @Test_Table AS TABLE (Test_Column VARCHAR(10));
INSERT INTO @Test_Table
SELECT '11' UNION ALL
SELECT '1.0' UNION ALL
SELECT '+5' UNION ALL
SELECT '-5' UNION ALL
SELECT '1 1' UNION ALL
SELECT '0d123' UNION ALL
SELECT '-4.55E-66' UNION ALL
SELECT '+4.55E+66' UNION ALL
SELECT '$50' UNION ALL
SELECT '-' UNION ALL
SELECT '+' UNION ALL
SELECT '.' UNION ALL
SELECT '$' UNION ALL
SELECT '\' UNION ALL
SELECT '2D2' UNION ALL
SELECT ',1,,1' UNION ALL
SELECT ',1,' UNION ALL
SELECT '1,' UNION ALL
SELECT '5.0e4' UNION ALL
SELECT '2e3' UNION ALL
SELECT NULL UNION ALL
SELECT '';


SELECT 
	Test_Column AS Input,
	ISNUMERIC(Test_Column) AS 'ISNUMERIC',
	ISNUMERIC(TRY_PARSE(Test_Column AS INT)) AS 'TRY_PARSE',
	ISNUMERIC(TRY_CAST(Test_Column AS INT)) AS 'TRY_CAST',
	CASE WHEN Test_Column NOT LIKE '%[^0-9]%' AND Test_Column<>'' THEN 1 ELSE 0 END AS CaseWithPredicate,
	ISNUMERIC(Test_Column+'.e0') AS '.e0_Trick'
FROM @Test_Table;

Output Result sets


Output Result sets

Based on our business requirements, we can wisely choose any of the above methods.

Conclusion

Thus, we saw what is ISNUMERIC Function in SQL server and what are the interesting result sets that it gave us and what are the other alternatives that we can use instead of ISNUMERIC Function. The above scripts are common for both Azure SQL DB and On-Premises and Unit Tested as well.

FOLLOW for Interesting Upcoming Posts!

Article Categories:
SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

Shares