Validate INT, SMALLINT, TINYINT & DECIMAL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Validate INT, SMALLINT, TINYINT & DECIMAL

Author

Topic

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2008-01-10 : 15:52:52

Hi all,

I found a UDF on the web to validate INT data contained in a VARCHAR field:

http://blog.sqlauthority.com/2007/08/11/sql-server-udf-validate-integer-function/

I modified it to accept NULL values and conform more closely to INT specification. Here is my modified function:


CREATE FUNCTION [dbo].[udfIsValidINT]
(
@Number VARCHAR(100)
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT, @ShiftByOne INT;
IF LEFT(@Number, 1) = '-'
SELECT @Number = SUBSTRING(@Number, 2, LEN(@Number)), @ShiftByOne=1;
SELECT @Number = COALESCE(@Number,'0'), @ShiftByOne = COALESCE(@ShiftByOne,0)
IF (PATINDEX('%[^0-9-]%', @Number) = 0
AND CHARINDEX('-', @Number) <= 1
AND @Number NOT IN ('.', '-', '+', '^')
AND LEN(@Number)>0
AND LEN(@Number)<11
AND @Number NOT LIKE '%-%')
SELECT @Ret = CASE WHEN CONVERT(BIGINT,@Number) - @ShiftByOne <= 2147483647
THEN 1 ELSE 0 END
ELSE
SET @Ret = 0
RETURN @Ret
END
GO
SELECT dbo.udfIsValidINT('2147483648')
SELECT dbo.udfIsValidINT('2147483647')
SELECT dbo.udfIsValidINT('-200')
SELECT dbo.udfIsValidINT('-2147483649')
SELECT dbo.udfIsValidINT('32900')
SELECT dbo.udfIsValidINT('1.79E+308')
GO

I also have a separate function for SMALLINT:


CREATE FUNCTION [dbo].[udfIsValidSMALLINT]
(
@Number VARCHAR(100)
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT, @ShiftByOne INT;
IF LEFT(@Number, 1) = '-'
SELECT @Number = SUBSTRING(@Number, 2, LEN(@Number)), @ShiftByOne=1;
SELECT @Number = COALESCE(@Number,'0'), @ShiftByOne = COALESCE(@ShiftByOne,0)
IF (PATINDEX('%[^0-9-]%', @Number) = 0
AND CHARINDEX('-', @Number) <= 1
AND @Number NOT IN ('.', '-', '+', '^')
AND LEN(@Number)>0
AND LEN(@Number)<6
AND @Number NOT LIKE '%-%')
SELECT @Ret = CASE WHEN CONVERT(INT,@Number) - @ShiftByOne <= 32677 THEN 1 ELSE 0 END
ELSE
SET @Ret = 0
RETURN @Ret
END
GO
SELECT dbo.udfIsValidSMALLINT('589')
SELECT dbo.udfIsValidSMALLINT('-200')
SELECT dbo.udfIsValidSMALLINT('-32900')
SELECT dbo.udfIsValidSMALLINT('32900')
SELECT dbo.udfIsValidSMALLINT('1.79E+308')

and one for TINYINT:


CREATE FUNCTION [dbo].[udfIsValidTINYINT]
(
@Number VARCHAR(100)
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT, @L TINYINT;
SET @L = LEN(@Number);
SET @Number = COALESCE(@Number,'0');
IF (PATINDEX('%[^0-9]%', @Number) = 0
AND @L>0
AND @L<4)
SELECT @Ret = CASE WHEN CONVERT(SMALLINT,@Number) < 256 THEN 1 ELSE 0 END
ELSE
SET @Ret = 0
RETURN @Ret
END
GO
SELECT dbo.udfIsValidTINYINT('256')
SELECT dbo.udfIsValidTINYINT('-1')
SELECT dbo.udfIsValidTINYINT('0')
SELECT dbo.udfIsValidTINYINT('255')
SELECT dbo.udfIsValidTINYINT('1.79E+308')

And, finally, a separate function for DECIMAL validation:


CREATE FUNCTION [dbo].[udfIsValidDECIMAL]
(
@Number VARCHAR(100),
@Scale TINYINT,
@Precision TINYINT
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT, @L TINYINT, @DSI TINYINT;
SET @Number = COALESCE(@Number,'0');
IF LEFT(@Number, 1) = '-'
SELECT	@Number = SUBSTRING(@Number, 2, LEN(@Number));
SET @L = LEN(@Number);
SET @DSI = @L - LEN(REPLACE(@Number,'.',''))
IF	(
		PATINDEX('%[^0-9.]%', @Number) = 0
		AND	CHARINDEX('-', @Number) = 0
		AND	@DSI <= 1
		AND	@L>0
		AND	@L<=@Scale+@DSI	+ CASE @DSI WHEN 1 THEN @L-CHARINDEX('.', @Number) ELSE 0 END
		AND @Scale - @Precision >= CASE @DSI WHEN 1 THEN CHARINDEX('.', @Number) - 1 ELSE @L END
	)
	SELECT @Ret = 1
ELSE
	SET @Ret = 0
	RETURN @Ret
END
GO
SELECT dbo.udfIsValidDECIMAL('256',2,0)
SELECT dbo.udfIsValidDECIMAL('-1',1,0)
SELECT dbo.udfIsValidDECIMAL('10.123456789123456789',18,17)
SELECT dbo.udfIsValidDECIMAL('10.123456789123456789',18,16)
SELECT dbo.udfIsValidDECIMAL('-255.0000000000000001',3,0)
SELECT dbo.udfIsValidDECIMAL('1.79E+308',9,2)

Node that the DECIMAL validation function specifically tests whether the input number can legally convert to a given decimal scale and precision. Converting a value of 0.234234 over to DECIMAL(1,0) will work, but SQL will truncate the actual decimals to fit it in that space. However, it will throw an error if you have too many whole digits.

On the whole, I was rather rushed to get these created, so there may be some errors I didn't notice. I'm interested in any improvements you guys can make to improve performance or make them cleaner.

Thanks for looking!

- Shane

nr
SQLTeam MVY

12543 Posts

Posted - 2008-01-11 : 05:28:58

Only glanced at the first one - doesn't it get rid of the - then tset for it?
I would have thought something like

select @Ret = 1
select @Number = ltrim(rtrim(@Number))
if patindex('%[^0-9-]%', @Number) <> 0
or len(@Number) - len(replace(@Number,'-','')) > 1
or charindex('-',@Number) > 1
or len(@Number) > 8
select @Ret = 0
else
if convert(bigint,@Number) not between -32,768 and 32,767
select @Ret = 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2008-01-11 : 13:46:02

Good call. That code was ported over from the original and I just didn't clean it up yet.

Thanks NR.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources