isnumeric() incorrectly identifies numeric values in MSSQL

  • I assumed that isnumeric() would tell me if the string was a number or not.
  • How wrong I was, when I tried multiplying the number by 2, I got: 
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '$12.34' to data type int.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Data used

  1. CREATE TABLE #temp (DATA VARCHAR(12));
  2. go
  3.  
  4. INSERT INTO #temp VALUES('$12.34');
  5. INSERT INTO #temp VALUES('1.2e3');
  6. INSERT INTO #temp VALUES('12d3');
  7. INSERT INTO #temp VALUES('12.34');
  8. INSERT INTO #temp VALUES('1234');
  9. INSERT INTO #temp VALUES('-1234');
  10. INSERT INTO #temp VALUES('123,456,789');
  11. INSERT INTO #temp VALUES('123,4,56,789');
  12. INSERT INTO #temp VALUES('1234abcd');
  13. INSERT INTO #temp VALUES('abcd1234');
  14. INSERT INTO #temp VALUES(CHAR(9));      --tab
  15. INSERT INTO #temp VALUES(CHAR(32));     --space
  16. INSERT INTO #temp VALUES(CHAR(42));     --star
  17. go
  18.  
  19. SELECT DATA FROM #temp;
  20. go
data
------------
$12.34
1.2e3
12d3
12.34
1234
-1234
123,456,789
123,4,56,789
1234abcd
abcd1234


*

(13 row(s) affected)

The last 3 items are:

  1. char(9) = a tab
  2. char(32) = a space
  3. char(42) = a star

isnumeric()

  • isnumeric() falsely interpret non-numeric letters and symbols (such as D, E and $), and even tabs (char(9)) as numeric, when they are not.
  • isnumeric() also gets confused with commas.
  1. SELECT DATA,
  2.        ISNUMERIC(DATA) AS 'Result of isnumeric',
  3.        CASE ISNUMERIC(SUBSTRING(DATA,1,1))
  4.             WHEN 0 THEN '1st is: Character'
  5.             WHEN 1 THEN '1st is: Numeric'
  6.        END AS '1st char is:',
  7.        CASE ISNUMERIC(DATA + 'e0')
  8.             WHEN 0 THEN 'No'
  9.             WHEN 1 THEN 'Yes'
  10.        END  AS 'Is really a number'
  11. FROM #temp
  12. go
data         Result of isnumeric 1st char is:      Is really a number
------------ ------------------- ----------------- ------------------
$12.34       1                   1st is: Numeric   No
1.2e3        1                   1st is: Numeric   No
12d3         1                   1st is: Numeric   No
12.34        1                   1st is: Numeric   Yes
1234         1                   1st is: Numeric   Yes
-1234        1                   1st is: Numeric   Yes
123,456,789  1                   1st is: Numeric   No
123,4,56,789 1                   1st is: Numeric   No
1234abcd     0                   1st is: Numeric   No
abcd1234     0                   1st is: Character No
	            1                   1st is: Numeric   No
             0                   1st is: Character No
*            0                   1st is: Character No

(13 row(s) affected)

Notice:

  1. The 1 in the column Result of isnumeric that is not lined up is because it's the char(9) or the tab character.
  2. Line 3: case isnumeric(substring(data,1,1)) returns numeric when it should not for:
    1. $
    2. -
  • The way to really test if a value is numeric is to actually convert to the number power 0, meaning multiplying it by 1. So 12.34e0 is equal to 12.34 * 1 or 12.34

Note the following problems:

  1. Line 2: isnumeric('$12.34') returns true
  2. Line 3: isnumeric(substring('$12.34',1,1)) returns true which is wrong. substring('$12.34',1,1) is the $ and it's not a number.
  3. Line 7: isnumeric('$12.34' + 'e0') returns false because you are trying to multiply $12.34 by 1 and this fails.

When testing for numeric data do NOT use isnumeric() by itself but instead use isnumeric(data + 'e0')