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