Technical Article

IsNumber fails cast while IsBigInt passes cast

,

When converting varchar data to bigint datatype, you can use IsNumeric() builtin function but it returns true even if a number string contains strange characters. It also does not do range checking. Just run the included script to create the IsBigInt() function in your database. If it returns true, you can be sure the string will cast to bigint.

/*
-- Tests pass isnumeric AND fail IsBigInt AND fail cast(vc as bigint)

-- range
SELECT IsNumeric('-9223372036854775809'), dbo.IsBigInt('-9223372036854775809')
SELECT IsNumeric('9223372036854775808'), dbo.IsBigInt('9223372036854775808')

-- invalid chars
SELECT IsNumeric('-5d2'), dbo.IsBigInt('-5d2')
SELECT IsNumeric('-5e2'), dbo.IsBigInt('-5e2')
SELECT IsNumeric('+3,4'), dbo.IsBigInt('+3,4')
SELECT IsNumeric('+3.4'), dbo.IsBigInt('+3.4')

-- pass this strange case
SELECT IsNumeric('00000000000000000000000000001'), dbo.IsBigInt('00000000000000000000000000001')
*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.IsBigInt') AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.IsBigInt
GO

CREATE FUNCTION dbo.IsBigInt (@a varchar(30))
returns bit
AS
BEGIN
-- Submitted to SqlServerCentral by William Talada
DECLARE
@s varchar(30),
@i int,
@IsNeg bit,
@valid int

-- assume the best
SET @valid = 1
SET @IsNeg=0
SET @s = ltrim(rtrim(@a))

-- strip OFF negative sign
IF len(@s) > 0
AND LEFT(@s, 1) = '-'
BEGIN
SET @IsNeg=1
SET @s = RIGHT(@s, len(@s) - 1)
END

-- strip OFF positive sign
IF len(@s) > 0
AND LEFT(@s, 1) = '+'
BEGIN
SET @s = RIGHT(@a, len(@a) - 1)
END

-- strip leading zeros
while len(@s) > 1 and left(@s,1) = '0'
set @s = right(@s, len(@s) - 1)

-- 19 digits max
IF len(@s) > 19 SET @valid = 0

-- the rest must be numbers only
SET @i = len(@s)

WHILE @i >= 1
BEGIN
IF charindex(substring(@s, @i, 1), '0123456789') = 0 SET @valid = 0

SET @i = @i - 1
END

-- check range
IF @valid = 1
AND len(@s) = 19
BEGIN
IF @isNeg = 1 AND @s > '9223372036854775808' SET @valid = 0
IF @IsNeg = 0 AND @s > '9223372036854775807' SET @valid = 0
END

RETURN @valid
END
go

Rate

2.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

2.5 (2)

You rated this post out of 5. Change rating