Yesterday I got a bad experience with IsNumeric built in function of SQL Server. The idea is I calculate some part of a given number in varchar data type (separated by slash) and convert it to integer. So I used IsNumeric to check each character to determine whether it is digit or not.
The above statement will return true which is unexpected. Hence this caused conversion error in my function. I don’t know whether this behavior is a bug or by design, but it is quite annoying.
CREATE FUNCTION IsDigit ( @char char(1) ) RETURNS bit AS BEGIN DECLARE @result as bit select @result= case when exists(select 1 where @char like '%[0-9]%') then 1 else 0 end return @result END GO
The above function will work just like Char.IsDigit in .NET, however if you need to evalute more character then this function will do
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
The dbo.IsReallyInteger function is taken from this forum.