There are a number of problems with the ISNUMERIC function in TSQL, some of which were causing us grief recently. So I went looking for a solution, and found a UDF at aspfaq. What I really needed though, was a check for integers, so I concocted one as follows:
----------------------------------------------------------------------------
--Purpose: Checks that the input string is really an integer of the specified type.
-- To be used in place of the ISNUMERIC function, as it can't be trusted.
-- See http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10194
--Inspired by: http://www.aspfaq.com/show.asp?id=2390
--Created: 10/20/05 by Oskar Austegard.
--Updated: 11/16/05 by Oskar Austegard - fixed a rather serious bug...
----------------------------------------------------------------------------
ALTER FUNCTION dbo.IsReallyInteger
(
@Num varchar(64), --Input string to be checked
@Type varchar(8) --Type of integer: bigint, int, smallint, tinyint
)
RETURNS BIT
BEGIN
--Get the absolute value of the number by removing a leading - or +
DECLARE @AbsNum varchar(64)
SET @AbsNum = CASE WHEN LEFT(@Num, 1) IN ('-', '+') THEN SUBSTRING(@Num, 2, LEN(@Num)) ELSE @Num END
--Remove leading zeros
WHILE LEN(@AbsNum) > 1 AND LEFT(@AbsNum, 1) = '0'
SET @AbsNum = SUBSTRING(@AbsNum, 2, LEN(@AbsNum))
SET @Length = LEN(@AbsNum)
--Reinsert the - in negative numbers
SET @Num = CASE WHEN LEFT(@Num, 1) = '-' THEN '-' + @AbsNum ELSE @AbsNum END
--Check for empty string or non-digits
IF @AbsNum = '' OR PATINDEX('%[^0-9]%', @AbsNum) > 0
RETURN 0
--Check limits by type
IF (@Type = 'bigint' AND (@Length < 19 OR (@Length = 19 AND (@AbsNum < '9223372036854775807' OR @Num = '-9223372036854775808'))))
OR (@Type = 'int' AND (@Length < 10 OR (@Length = 10 AND (@AbsNum < '2147483648' OR @Num = '-2147483648'))))
OR (@Type = 'smallint' AND (@Length < 5 OR (@Length = 5 AND (@AbsNum < '32768' OR @Num = '-32768'))))
OR (@Type = 'tinyint' AND LEFT(@Num, 1) <> '-' AND (@Length < 3 OR (@Length = 3 AND @AbsNum < '256')))
RETURN 1 --Success
--Else
RETURN 0 --Failure
END
I then wrap this function in another for the most common usage:
ALTER FUNCTION dbo.IsReallyInt
(
@Num varchar(64) --Input to be checked
)
RETURNS BIT
BEGIN
RETURN dbo.IsReallyInteger(@Num, 'int')
END
Labels: sql
