SQL Modulus 10 User Function

[Spot]

Being completely lazy, has anyone written an SQL User function to
calculate a Modulus 10 check digit on a variable length number? I sure
could use a copy.

[Jonathan Ball]

Post by Spot
Being completely lazy, has anyone written an SQL User function to
calculate a Modulus 10 check digit on a variable length number? I sure
could use a copy.

You owe me a beer.

create function mylib.mod10 (modnumber bigint) returns smallint
language sql
begin
declare wrksum smallint default 0;
declare numlen smallint;
declare iter smallint default 0;
set numlen = char_length(modnumber);
while iter < numlen do
set iter = iter + 1;
if mod(iter,2) = 0 then
set wrksum = wrksum + smallint(substr(char(modnumber),numlen -
iter + 1,1));
else
set wrksum = wrksum +
case substr(char(modnumber),numlen - iter + 1,1)
when '1' then 2
when '2' then 4
when '3' then 6
when '4' then 8
when '5' then 1
when '6' then 3
when '7' then 5
when '8' then 7
when '9' then 9
else 0 end;
end if;
end while;
return smallint(case mod(wrksum,10) when 0 then 0 else 10 -
mod(wrksum,10) end);
end;

There is no guarantee, endorsement, or warranty of any kind, express or
implied (including specifically no warrant of merchantability or of
fitness for a particular purpose) concerning this code, but it appears
to me to work, and you owe me the beer regardless.

[Spot]

Thanks Jonathan!

Good News : I'll gladly pay up for the beer the next time your in
Chicago.
Bad News: It doesn't quite work.

I ran some test values thru with known check digits. The MOD10
column is the result from the UDF and Check_Digit is what they
should be.

Value MOD10 Check_Digit
506000632872 7 5
03878110033 4 6
85143100137 5 7

Thanks much for the effort, I had hoped someone just had an existing
routine that they could share.

[Jonathan Ball]

Post by Spot
Thanks Jonathan!
Good News : I'll gladly pay up for the beer the next time your in
Chicago.
Bad News: It doesn't quite work.
I ran some test values thru with known check digits. The MOD10
column is the result from the UDF and Check_Digit is what they
should be.
Value MOD10 Check_Digit
506000632872 7 5
03878110033 4 6
85143100137 5 7
Thanks much for the effort, I had hoped someone just had an existing
routine that they could share.

You sure they're asking for Mod10? Here's what the IBM
page that deebeetwo linked says about the computation:

1. Multiply the units position and every alternate
position of the base number by 2.
2. Add the digits in the products to the digits in the
base number that were not multiplied.
3. Subtract the sum from the next higher number ending
in zero.

Taking your third number above

8 5 1 4 3 1 0 0 1 3 7
16 5 2 4 6 1 0 0 2 3 14

Summing the digits,
1 + 6 + 5 + 2 + 4 + 6 + 1 + 0 + 0 + 2 + 3 + 1 + 4 = 35

The next higher power of 10 is 40, so 40 - 35 = 5.
That's what my routine calculates.

I used the routine against the first 15 digits of all
my credit and ATM cards, and in every case it returned
the correct 16th digit of the card number.

[Spot]

Post by Jonathan Ball
You sure they're asking for Mod10? Here's what the IBM

Like I said, UPC is slightly different from true Modulus 10 which I
didn't know at the start of this thread. If you check the first link in
my last message, it mentions multiplying the base number by three not
two as in true Mod10. Your version works for Credit Cards but mine
works for products. I was able to verify it against our product
database.

[Jonathan Ball]

Post by Spot

Post by Jonathan Ball
You sure they're asking for Mod10? Here's what the IBM

Like I said, UPC is slightly different from true Modulus 10 which I
didn't know at the start of this thread. If you check the first link in
my last message, it mentions multiplying the base number by three not
two as in true Mod10. Your version works for Credit Cards but mine
works for products. I was able to verify it against our product
database.

Okay, try this:

create function my.upcmod10 (modnumber bigint) returns
smallint language sql contains sql
begin
declare wrksum smallint default 0;
declare numlen smallint;
declare iter smallint default 0;
set numlen = char_length(modnumber);
while iter < numlen do
set iter = iter + 1;
if mod(iter,2) = 0 then
set wrksum = wrksum +
smallint(substr(char(modnumber),numlen -
iter + 1,1));
else
set wrksum = wrksum +
smallint(substr(char(modnumber),numlen -
iter + 1,1)) * 3;
end if;
end while;
return smallint(mod(10 - mod(wrksum,10),10));
end;

[d***@yahoo.com]

Post by Spot
Being completely lazy, has anyone written an SQL User function to
calculate a Modulus 10 check digit on a variable length number? I sure
could use a copy.

Perhaps these links could be helpful:

http://groups.google.com/group/comp.sys.ibm.as400.misc/browse_frm/thread/3e0c6f0523458a25/dea4b263ae7e0805?lnk=st&q=+Modulus+10+check+digit+&rnum=1#dea4b263ae7e0805

http://groups.google.com/group/comp.sys.ibm.as400.misc/browse_frm/thread/8559b6e13da4e0e/1d56f89eae64b9de?lnk=st&q=+Modulus+10+check+digit+&rnum=7#1d56f89eae64b9de

[Spot]

Thanks Jonathan and Deebee,

With some minor alteration, I got this to work the way I needed.

See http://www.uc-council.org/ean_ucc_system/education_support/cdc.html

I needed to calculate EAN's and UPC's which are a little different than
the IBM Published Modulus 10 calculation.
(per deebee see
http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/QB3AUI01/3.27.1.4 )

IBM said to multiply the result of the odd numbers by two but the UPC
multiplies by three.

The tested results to calculate a check digit for UPC, EAN,
ISBN, etc are:


create function mylib.mod10 (modnumber bigint) returns smallint
language sql
begin
declare sumeven int default 0;
declare sumodd int default 0;
declare numlen int;
declare iter int default 0;
set numlen = char_length(modnumber);
while iter < numlen do
set iter = iter + 1;
if mod(iter,2) = 0 then
set sumeven = sumeven + smallint(substr(char(modnumber),
numlen - iter + 1,1));
else
set sumodd = sumodd + smallint(substr(char(modnumber),
numlen - iter + 1,1));
end if;
end while;
set sumodd = sumodd * 3;
set sumeven = sumeven + sumodd;
return smallint(case mod(sumeven,10)
when 0 then 0
else 10 - mod(sumeven,10) end);
end;