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