Why You Should Avoid Using ISNUMERIC in SQL Server

I’ve been doing a lot of T-SQL code reviews and i find it over and over again the use of ISNUMERIC function to check if a column contains a valid numbers.

You need to avoid using it because it is not what you expect. Microsoft documentation says:

ISNUMERIC returns 1 for some characters that aren’t numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($).

During testing, i was surprised that the scientific notation was also consider as numeric and it was not mentioned in the documentation.

Australian Postal Codes (4 digits)

Here’s a quick demo, supposed you have a postal code column and you want to check whether it contains a valid postal codes. In Australia, postal code has 4 digit. For brevity, this example is just for testing but real world would still be similar.

Let us create a test table that includes all scenarios for nvarchar data type.

DROP TABLE IF EXISTS #PostCode;

CREATE TABLE #Postcode (
    Postcode nvarchar(4),
    [Description] nvarchar(50)
);

INSERT INTO #Postcode (Postcode, [Description])
VALUES 
('1234', 'Standard Numeric'),
('567 ', 'Numeric with space'),
('1e3',  'Scientific Notation'),
('$10',  'Currency Symbol'),
('A123', 'Alpha-Numeric'),
('1.2',  'Decimal Number'),
('9999', 'Standard Numeric'),
('-12',  'Negative Number'),
('+1',  'Positive Number');

Using ISNUMERIC (the wrong way)

Let us use ISNUMERIC function. And yes, some developers using it in WHERE clause which is a really bad idea.

SELECT Postcode, [Description], 'IsNumeric' as Method
FROM #Postcode
WHERE ISNUMERIC(Postcode) = 1;

Postcode	Description	Method
1234	Standard Numeric	IsNumeric
567	Numeric with space	IsNumeric
1e3	Scientific Notation	IsNumeric
$10	Currency Symbol	IsNumeric
1.2	Decimal Number	IsNumeric
9999	Standard Numeric	IsNumeric
-12	Negative Number	IsNumeric
+1	Positive Number	IsNumeric

You will see that it only filters out the postcode A123, which is an invalid postal code in Australia. However, it returns also other values that are not numeric, we only expect 1234, 9999 since these are the only valid 4 digit numeric.

I’ve noticed AI suggesting this to the developers as well even though it really shouldn’t. Maybe because AI picks up the common use of ISNUMERIC function. Be careful relying too much on AI. This could a cause data quality issue if you are not careful.

The Right Way: Pattern Matching

One way to fix this issue is to pattern matching. This is the most reliable way to make sure the string only contains numeric digits.

SELECT Postcode, [Description], 'Like Pattern' as Method
FROM #Postcode
WHERE Postcode LIKE '[0-9][0-9][0-9][0-9]';

Postcode	Description	Method
1234	Standard Numeric	Like Pattern
9999	Standard Numeric	Like Pattern

This is what we expected to return: a valid numeric digits.

Alternative #1: Computed Column

Another option is to use computed column. Do your calculation in a new column.

ALTER TABLE #Postcode 
ADD IsStrictNumeric AS (CASE WHEN postcode NOT LIKE '%[^0-9]%' AND postcode <> '' THEN 1 ELSE 0 END);

then when querying it, use the new column IsStrictNumeric or whatever column you want to name it.

SELECT * FROM #Postcode
WHERE isStrictNumeric = 1;

this return a valid “numeric” digits just like the pattern matching above.

Alternative #2: CHECK Constraint

Instead of trying to filter out bad data in every SELECT query, another alternative is to use constraints to prevent bad data from ever entering the table in the first place. But the downside is that you must either cleanup existing bad data before applying the check constraint or apply the constraint WITH NOCHECK to ignore existing bad data and enforces the constraint rule only for future inserts.

Summary

So there you have it. Avoid using ISNUMERIC function when validating numeric data. Make sure you validate the data on application level before it hits the database. Issues like this can be difficult to troubleshoot and fix later, and they can lead to data quality problems.