Nov 5, 2013

How to check a given data or column is type of integer or not in sql server?

Sql server doesn’t support a function like IsInteger.  We can test a given data or column is type of int by using the function SQL_VARIANT_PROPERTY. For example:

SELECT SQL_VARIANT_PROPERTY(25,'BaseType')
Output: int

Creating IsInteger function in sql server

CREATE FUNCTION IsInteger(
    @Data AS SQL_VARIANT
)
RETURNS BIT
AS
BEGIN

    DECLARE @IsInt AS BIT
   
    IF SQL_VARIANT_PROPERTY(@Data,'BaseType') = 'int'
         SET  @IsInt = 1
    ELSE
         SET  @IsInt = 0
        
    RETURN @IsInt
   
END

Examples:
DECLARE @Var1 INTEGER = 25
DECLARE @Var2 VARCHAR(5) = '25'
DECLARE @Var3 BIT = 1
DECLARE @Var4 BIGINT = 25

SELECT
    dbo.IsInteger(@Var1),
    dbo.IsInteger(@Var2),
    dbo.IsInteger(@Var3),
    dbo.IsInteger(@Var4)

Output:
1   0   0   0

Note: Maximum and minimum value of integer constant in sql server is 2147483647 and -2147483648 respectivily. Any number lager or smaller than this values are not integer constant. For example:

SELECT
    dbo.IsInteger(2147483647),
    dbo.IsInteger(-2147483648),
    dbo.IsInteger(2147483648),
    dbo.IsInteger(-2147483649)

Output:
1   1   0   0

No comments:

Post a Comment