What is wrong with IsNumeric()?

T-SQL

T-SQL's ISNUMERIC() function has a problem. It can falsely interpret non-numeric letters and symbols (such as D, E, and £), and even tabs (CHAR(9)) as numeric. To get around this, you can wrap your own functions to determine numeric / integer, as follows:

CREATE FUNCTION dbo.isReallyNumeric
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))

DECLARE @pos TINYINT

SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))

RETURN CASE
WHEN PATINDEX('%[^0-9.-]%', @num) = 0
AND @num NOT IN ('.', '-', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
AND
(
((@pos = LEN(@num)+1)
OR @pos = CHARINDEX('.', @num))
)
THEN
1
ELSE
0
END
END
GO

CREATE FUNCTION dbo.isReallyInteger
(
@num VARCHAR(64)
)
RETURNS BIT
BEGIN
IF LEFT(@num, 1) = '-'
SET @num = SUBSTRING(@num, 2, LEN(@num))

RETURN CASE
WHEN PATINDEX('%[^0-9-]%', @num) = 0
AND CHARINDEX('-', @num) <= 1
AND @num NOT IN ('.', '-', '+', '^')
AND LEN(@num)>0
AND @num NOT LIKE '%-%'
THEN
1
ELSE
0
END
END
GO

Of course you could use the same kind of logic within T-SQL, e.g. in a WHERE clause or CASE expression, so you don't have to use a user-defined function (for those of you still on 7.0 or earlier). Here is the sample I used for testing:

SET NOCOUNT ON

CREATE TABLE #CHARS
(
x INT IDENTITY(1,1),
c VARCHAR(32)
)

DECLARE @i INT
SET @i = 1
WHILE @i < 255
BEGIN
INSERT #CHARS(c) SELECT CHAR(@i)
SET @i = @i + 1
END

INSERT #CHARS(c) VALUES('€')
INSERT #CHARS(c) VALUES('€5.6')
INSERT #CHARS(c) VALUES('-€5.6')
INSERT #CHARS(c) VALUES('6^7')
INSERT #CHARS(c) VALUES('-6^7')
INSERT #CHARS(c) VALUES('-6^7+4')
INSERT #CHARS(c) VALUES('-6^7+4.3')
INSERT #CHARS(c) VALUES('-4.3')
INSERT #CHARS(c) VALUES('4.3')
INSERT #CHARS(c) VALUES('-4')
INSERT #CHARS(c) VALUES('4')
INSERT #CHARS(c) VALUES('0E30')
INSERT #CHARS(c) VALUES('0002')
INSERT #CHARS(c) VALUES('00E2')
INSERT #CHARS(c) VALUES('00F2')
INSERT #CHARS(c) VALUES('3.4')
INSERT #CHARS(c) VALUES('3.475')
INSERT #CHARS(c) VALUES('43243.404')
INSERT #CHARS(c) VALUES('43243.40.4')
INSERT #CHARS(c) VALUES('3.E4')
INSERT #CHARS(c) VALUES('32')
INSERT #CHARS(c) VALUES('E.')
INSERT #CHARS(c) VALUES('$4.56')
INSERT #CHARS(c) VALUES('-$4.56')
INSERT #CHARS(c) VALUES('-1-')
INSERT #CHARS(c) VALUES('-1.4.5')
INSERT #CHARS(c) VALUES('1.4.5')
INSERT #CHARS(c) VALUES('+46')
INSERT #CHARS(c) VALUES('46+')

SELECT
Char_Number = CASE
WHEN x <= 255 THEN RTRIM(x)
ELSE '-' END,
Char_Value = RTRIM(CASE
WHEN x <= 255 THEN CHAR(x)
ELSE c END),
is_numeric = ISNUMERIC(c),
is_really_numeric = dbo.isReallyNumeric(c),
is_really_integer = dbo.isReallyInteger(c)
FROM
#CHARS
WHERE
ISNUMERIC(c) = 1
OR dbo.isReallyNumeric(c) = 1
OR dbo.isReallyInteger(c) = 1
ORDER BY CASE
WHEN x <= 255 THEN x
ELSE 999 END,
3,4 DESC,5 DESC

DROP TABLE #CHARS
GO

The results:

Char_Number	Char_Value	Is_Numeric	Is_Really_Numeric	Is_Really_Integer
9	(tab)	1	0	0
10	(line feed)	1	0	0
11		1	0	0
12		1	0	0
13	(carriage return)	1	0	0
36	$	1	0	0
43	+	1	0	0
44	,	1	0	0
45	-	1	0	0
46	.	1	0	0
48	0	1	1	1
49	1	1	1	1
50	2	1	1	1
51	3	1	1	1
52	4	1	1	1
53	5	1	1	1
54	6	1	1	1
55	7	1	1	1
56	8	1	1	1
57	9	1	1	1
128	€	1	0	0
160		1	0	0
163	£	1	0	0
164	¤	1	0	0
165	¥	1	0	0
255	ÿ	1	0	0
-	-4	1	1	1
-	4	1	1	1
-	32	1	1	1
-	0002	1	1	1
-	-4.3	1	1	0
-	4.3	1	1	0
-	3.4	1	1	0
-	3.475	1	1	0
-	43243.404	1	1	0
-	3.E4	1	0	0
-	0E30	1	0	0
-	00E2	1	0	0
-	$4.56	1	0	0
-	-$4.56	1	0	0
-	+46	1	0	0
-	€5.6	1	0	0
-	-€5.6	1	0	0

Another indirect problem relating to ISNUMERIC() on SQL Server 7.0 systems arises when SQL Mail is enabled. See this thread for more information on that one.

VBScript / JavaScript

VBScript's IsNumeric() function does not actually determine whether an expression contains only numeric digits; it determines whether the expression could be evaluated as a number. In the following cases, isNumeric returns true:

<%
response.write isNumeric("3e4") & ""
response.write isNumeric("3d4") & ""
response.write isNumeric("&H05") & ""
response.write isNumeric("$45,327.06") & ""
%>

To see why, you should notice that d was once used to denote double precision, e is used to express scientific notation, that &H is used to express a hex number, and of course $ , and . are used to express currency (other currency symbols in other regional settings will be subject to the same problem). You will also see this problem with numbers beginning with & (e.g. Octal numbers), numbers with parentheses (denoting negative), and numbers with leading + and - signs.

Observe the following output:

<%
response.write 1 * 3e4 & ""
response.write 1 * clng("3d4") & ""
response.write 1 * &H05 & ""
response.write 1 * cdbl("$45,327.06") & ""
%>

(While the use of the d and e characters produce the same result, it should be noted that d requires explicit conversion (e.g. to Long) to work correctly, while e is implicitly converted.)

So, to test if these 'numbers' really are numeric, one workaround is to write your own isNumeric() function that simply steps through the expression and tests each character (digits 0 through 9 have ASC values between 48 and 57), e.g.:

<%
function isReallyNumeric(str)
isReallyNumeric = true
for i = 1 to len(str)
d = mid(str, i, 1)
if asc(d) < 48 OR asc(d) > 57 then
isReallyNumeric = false
exit for
end if
next
end function

response.write isReallyNumeric("3e4") & ""
response.write isReallyNumeric("3d4") & ""
response.write isReallyNumeric("&H05") & ""
response.write isReallyNumeric("$45,327.06") & ""
' and a sanity check:
response.write isReallyNumeric("1234") & ""
%>

You'll have to adjust if you want to accept certain characters as 'numeric', e.g. commas, decimal points, + and - signs, etc.

Note that JScript/JavaScript's isNaN() function has similar limitations; it also sees 'e' as scientific notation, for example.



// home // advanced search // news // categories // sql build chart // downloads // statistics
ASP FAQ

What is wrong with IsNumeric()?

Related Articles