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.
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 the job.
```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.