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

The topic describes the SA0103 analysis rule.

Message

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

Description

The rule checks the script and reports queries that use the ISNUMERIC function.

A common use case is to convert character strings to integers and join to other tables on the integer value.
However, in some cases a character string column contains non-integral values, which are not convertible and lead to an error on the attempted join.

The ISNUMERIC() function would not be best in this case as it accepts floating point and monetary number formats which would still produce error when converted to integer value.
A better solution would be to use LIKE to verify the validity of the text before attempting to convert it to an integer and guarantee that the query will not fail.

SQL
 1DECLARE @i INT, @str VARCHAR(100)
 2SET @str = '1234'
 3
 4-- Unsigned
 5SET @i = CASE WHEN LTRIM(RTRIM(@str)) NOT LIKE '%[^0-9]%'
 6    THEN CAST(@str AS INT) ELSE NULL END
 7
 8-- Signed
 9SET @I = CASE WHEN LTRIM(@str) LIKE '[-0-9]%'
10        AND SUBSTRING(LTRIM(RTRIM(@str)), 2, 8000) NOT LIKE '%[^0-9]%'
11    THEN CAST(@str AS INT) ELSE NULL END

How to fix

Use LIKE predicate to verify the validity of the text instead of instead of ISNUMERIC, before attempting to convert it to an integer.

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.
Categories

Design Rules, Bugs

Additional Information
Example Test SQL
SQL
1SELECT City, PostalCode
2FROM Person.Address 
3WHERE ISNUMERIC(PostalCode)<> 1;
4
5SELECT ISNUMERIC('120,00$');
6
7SELECT 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
See Also

Other Resources