Beware of IsNumeric !

Recently 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.

select isnumeric('-')

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