SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Validate INT, SMALLINT, TINYINT & DECIMAL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lazerath
Constraint Violating Yak Guru

USA
319 Posts

Posted - 01/10/2008 :  15:52:52  Show Profile  Reply with Quote
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

Edited by - lazerath on 01/10/2008 15:55:20

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 01/11/2008 :  05:28:58  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
319 Posts

Posted - 01/11/2008 :  13:46:02  Show Profile  Reply with Quote
Good call. That code was ported over from the original and I just didn't clean it up yet.

Thanks NR.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000