Alternatives · Basic Logics · Real Scenario Logic's

Alternate to Interesting ISNUMERIC Function Results in SQL Server

Introduction

In this blog post, we are going to see what is ISNUMERIC Function in SQL server and what are the interesting result sets that it gives for some inputs and what are the other alternatives that we can use instead of ISNUMERIC Function.

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 Pic

Output Result sets

The SQL ISNUMERIC Function conveys that the string might be a valid number in many scenarios that are not actually a valid number. Due to this, we cannot able to ensure ISNUMERIC functionality to all our all cases.

What are the 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 Pic

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 mentioned scripts that are common for both Azure SQL DB and On-Premises. Unit Tested as well.

Thanks for reading and Follow Blog and Show your Support for many more interesting upcoming Posts!

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s