Number Validation Function Required

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

SQL Server 2000 Forums

SQL Server Development (2000)

Number Validation Function Required

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2006-07-05 : 03:52:15

I have a 13 digit number which I need to validate by the
following steps. I have created a Scalar Function (see below)
for this task, but I don’t get the correct answer, yet if I take
individual snippets from the function and run it as a select
query I get 100% correct results. Could someone PLEASE tell me
what I am doing wrong? (I am new to writing SQL Functions)
I need to add that the ‘number’ is in fact stored as a varchar in
a table.
Steps Required:
a) Add all the digits in the odd positions (excluding last
digit).
b) Move the number in even positions into a field and multiply
the number by 2.
c) Add the digits of the result in b).
d) Add the answer in a) to the answer in c).
e) Subtract the second digit from 10.

CREATE FUNCTION dbo.ValidateNo
(@No varchar)
RETURNS int
AS
BEGIN

DECLARE @Step1 int
DECLARE @Step2 int
DECLARE @Step3 int
DECLARE @Step4 int
DECLARE @Step5 int

Set @Step1 =((Left(@No,1)*1)
+(SUBSTRING(@No,3,1)*1)
+(SUBSTRING(@No,5,1)*1)
+(SUBSTRING(@No,7,1)*1)
+(SUBSTRING(@No,9,1)*1)
+(SUBSTRING(@No,11,1)*1))

Set @Step2 =(SUBSTRING(@No,2,1)
+SUBSTRING(@No,4,1)
+SUBSTRING(@No,6,1)
+SUBSTRING(@No,8,1)
+SUBSTRING(@No,10,1)
+SUBSTRING(@No,12,1))*2

Set @Step3 = case len(@step2)
when 7 then
(Left(convert(varchar,@Step2),1))*1
+(SUBSTRING(convert(varchar,@Step2),2,1))*1
+(SUBSTRING(convert(varchar,@Step2),3,1))*1
+(SUBSTRING(convert(varchar,@Step2),4,1))*1
+(SUBSTRING(convert(varchar,@Step2),5,1))*1
+(SUBSTRING(convert(varchar,@Step2),6,1))*1
+(SUBSTRING(convert(varchar,@Step2),7,1))*1
when 6 then
(Left(convert(varchar,@Step2),1))*1
+(SUBSTRING(convert(varchar,@Step2),2,1))*1
+(SUBSTRING(convert(varchar,@Step2),3,1))*1
+(SUBSTRING(convert(varchar,@Step2),4,1))*1
+(SUBSTRING(convert(varchar,@Step2),5,1))*1
+(SUBSTRING(convert(varchar,@Step2),6,1))*1
else 0
end

Set @Step4 = @Step1 + @Step3

Set @Step5 = 10 - (Right(@Step4,1))

RETURN (@Step5)
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-05 : 04:20:54

Are you trying to validate EAN13 barcode ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-05 : 05:17:30

It seems that it is the last step,

Set @Step5 = 10 - (Right(@Step4, 1))

that is faulty. What if

Right(@Step4,1)

is Zero? Then @Step5 returns 10. Change

RETURN (@Step5)

RETURN (@Step5 % 10)

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-07-05 : 05:21:31

"Are you trying to validate EAN13 barcode ?"

That's like a mate of mine who could tell the difference between Single-Density and Double-Density floppies by looking at the flux!!

Kristen
Test

22859 Posts

Posted - 2006-07-05 : 05:23:49

CREATE FUNCTION dbo.ValidateNo
(@No varchar)

This may be bad news without a size on the "varchar" definition, and even if it works it would be better-practice to explicitly state the required size

CREATE FUNCTION dbo.ValidateNo
(@No varchar(13))

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-05 : 05:33:50

Or use this code instead. It is a generic Modula 10 check function.
Works for EAN8, EAN 13, swedish social security numbers, bank and postal OCR transfer codes and so on...

CREATE FUNCTION dbo.fnModula10Check
(
	@Code VARCHAR(8000)
)
RETURNS BIT
AS

BEGIN
	IF PATINDEX('%[^0-9]%', @Code) > 0
		RETURN NULL

	DECLARE @Index SMALLINT,
		@SUM INT,
		@Number TINYINT

	SELECT	@Index = LEN(@Code) - 1,
		@Sum = 0

	WHILE @Index > 0
		SELECT	@Number = 	CASE @Index % 2
						WHEN 0 THEN CONVERT(TINYINT, SUBSTRING(@Code, @Index, 1))
						ELSE 2 * CONVERT(TINYINT, SUBSTRING(@Code, @Index, 1))
					END,
			@Sum = @Sum + (@Number / 10) + (@Number % 10),
			@Index = @Index - 1
	RETURN CASE WHEN (CONVERT(TINYINT, RIGHT(@Code, 1)) + @Sum) % 10 = 0 THEN 1 ELSE 0 END
END

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-05 : 05:40:24

If you are trying to calculate the check digit for a modula 10 text, use this code for numeric return

CREATE FUNCTION dbo.fnModula10Checksum
(
	@Code VARCHAR(8000)
)
RETURNS TINYINT
AS

BEGIN
	IF PATINDEX('%[^0-9]%', @Code) > 0
		RETURN NULL

	DECLARE @Index SMALLINT,
		@SUM INT,
		@Number TINYINT

	SELECT	@Index = LEN(@Code),
		@Sum = 0

	WHILE @Index > 0
		SELECT	@Number = 	CASE @Index % 2
						WHEN 0 THEN CONVERT(TINYINT, SUBSTRING(@Code, @Index, 1))
						ELSE 2 * CONVERT(TINYINT, SUBSTRING(@Code, @Index, 1))
					END,
			@Sum = @Sum + (@Number / 10) + (@Number % 10),
			@Index = @Index - 1
	RETURN CASE WHEN @Sum % 10 = 0 THEN 0 ELSE 10 - @Sum % 10 END
END

and this one for varchar return

CREATE FUNCTION dbo.fnModula10Checksum
(
	@Code VARCHAR(8000)
)
RETURNS VARCHAR(1)
AS

BEGIN
	IF PATINDEX('%[^0-9]%', @Code) > 0
		RETURN NULL

	DECLARE @Index SMALLINT,
		@SUM INT,
		@Number TINYINT

	SELECT	@Index = LEN(@Code),
		@Sum = 0

	WHILE @Index > 0
		SELECT	@Number = 	CASE @Index % 2
						WHEN 0 THEN CONVERT(TINYINT, SUBSTRING(@Code, @Index, 1))
						ELSE 2 * CONVERT(TINYINT, SUBSTRING(@Code, @Index, 1))
					END,
			@Sum = @Sum + (@Number / 10) + (@Number % 10),
			@Index = @Index - 1
	RETURN CASE WHEN @Sum % 10 = 0 THEN '0' ELSE CHAR(58 - @Sum % 10) END
END

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources