SA0103 : Avoid using ISNUMERIC function as it accepts floating point and monetary number

Introduction

Using the ISNUMERIC function incorrectly to check if strings can be safely converted to integers may lead to errors and unexpected results.

Description

In SQL Server, developers sometimes need to convert character strings to integers for operations like joins. A typical solution is to use ISNUMERIC to validate if a string can be converted. However, ISNUMERIC identifies a wider range of numeric formats, including floating-point and monetary values, which aren’t appropriate for integer conversion. Attempting to convert these to integers may result in errors, causing queries to fail.

For example:

-- Problematic usage of ISNUMERIC
DECLARE @str VARCHAR(50) = '123.45';
IF ISNUMERIC(@str) = 1
BEGIN
    SELECT CAST(@str AS INT);
END

This example shows how ISNUMERIC would acknowledge ‘123.45’ as a numeric value, leading to conversion errors because it’s not an integer.

Potential conversion errors when non-integer numbers are verified by ISNUMERIC and then converted to integers.
Inadequate handling of input that includes non-integral numeric formats, risking failed query executions.

How to fix

To ensure the validity of text for integer conversion, use a LIKE predicate instead of ISNUMERIC .

Follow these steps to address the issue:

1.Identify the columns or variables containing character strings that need to be converted to integers.

2.Replace the use of ISNUMERIC with a LIKE pattern to match only strings that are valid integers. Use the pattern '[0-9]%' to ensure only strings with numeric digits are considered.

3.Proceed with conversion using CAST or CONVERT only when the string matches the integer pattern.

For example:

DECLARE @str VARCHAR(50) = '123.45';
IF @str LIKE '[0-9]%'
BEGIN
    SELECT CAST(@str AS INT);
END

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

20 minutes per issue.

Additional Information

ISNUMERIC (Transact-SQL)

Example Test SQL

SELECT City, PostalCode
FROM Person.Address
WHERE ISNUMERIC(PostalCode)<> 1;

SELECT ISNUMERIC('120,00$');

SELECT ISNUMERIC('120,00$') /*IGNORE:SA0103*/;

Analysis Results

	Message	Line	Column
1	SA0103 : Avoid using ISNUMERIC function as it accepts floating point and monetary number.	3	6
2	SA0103 : Avoid using ISNUMERIC function as it accepts floating point and monetary number.	5	7

Analysis Rules