Monday, 6 January 2014

SQL Function to identify Integer Values (ISINTEGER())


Recently I have come across a situation to identify integer values as part of SQL Server validation program. In SQL Server, we don’t have any built-in function to perform these validations.

Below mentioned user defined function identifies integer values and returns bit values based on the input.

Function Code

IF OBJECT_ID ('UDF_IsInteger','FN') IS NOT NULL
   DROP FUNCTION UDF_IsInteger;
GO
CREATE FUNCTION dbo.UDF_IsInteger(@Value VARCHAR(100))
RETURNS BIT
AS
BEGIN
 
DECLARE @Res BIT

SELECT @Res =
CASE
    WHEN ISNUMERIC(@Value) = 0     THEN 0
    WHEN @Value LIKE '%[^-+ 0-9]%' THEN 0
    WHEN CAST(@Value AS NUMERIC(38, 0))
      NOT BETWEEN -2147483648. AND 2147483647. THEN 0
    ELSE 1
  END

RETURN @Res

END

Execution Results:

SELECT dbo.UDF_IsInteger('123')

-- 1

SELECT dbo.UDF_IsInteger('Hi')

--0