Click or drag to resize

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 if the ISNUMERIC function is used.

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 suchs as 'ACCT', which error on the attempted join.

A first though would be to try ISNUMERIC(), but that also accepts floating point and monetary number formats which would still error. Instead, it is better 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

Scope

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

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0103

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information
Example Test Script
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

 MessageLineColumn
1SA0103 : Avoid using ISNUMERIC function as it accepts floating point and monetary number.36
2SA0103 : Avoid using ISNUMERIC function as it accepts floating point and monetary number.57
See Also

Other Resources