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