RossWindows
Que?
- Local time
- Yesterday, 22:22
- Joined
- Feb 25, 2008
- Messages
- 410
I found this function online at gist.github.com/1371323
It was written for PL/SQL. I'm wondering if it can be adapted to work in MS SQL. I've tried a bunch of stuff, but I just can't get the validator to 'like' it.
Any ideas?
It was written for PL/SQL. I'm wondering if it can be adapted to work in MS SQL. I've tried a bunch of stuff, but I just can't get the validator to 'like' it.
Any ideas?
Code:
function ups1z(p_tracking_number varchar2) return boolean is
trk varchar2(18);
tot number := 0;
even number := 0;
odd number := 0;
num number := 0;
chk number := 0;
chr varchar2(1);
val number := 0;
begin
--if the data passed is 18 digits then strip everything but valid UPS tracking number characters.
if length(p_tracking_number) = 18 then
for x in 1..length(p_tracking_number) loop
chr := upper(substr(p_tracking_number,x,1));
if chr in('0','1','2','3','4','5','6','7','8','9','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z') then
trk := trk || chr;
end if;
end loop;
end if;
--if the stripped data is still 18 digits then check it against the UPS check digit formula
if length(trk) = 18 then
if substr(trk,1,2) = '1Z' then
for y in 3..17 loop
chr := substr(trk,y,1);
case
when chr = '0' then val := '0';
when chr = '1' then val := '1';
when chr = '2' then val := '2';
when chr = '3' then val := '3';
when chr = '4' then val := '4';
when chr = '5' then val := '5';
when chr = '6' then val := '6';
when chr = '7' then val := '7';
when chr = '8' then val := '8';
when chr = '9' then val := '9';
when chr = 'A' then val := '2';
when chr = 'B' then val := '3';
when chr = 'C' then val := '4';
when chr = 'D' then val := '5';
when chr = 'E' then val := '6';
when chr = 'F' then val := '7';
when chr = 'G' then val := '8';
when chr = 'H' then val := '9';
when chr = 'I' then val := '0';
when chr = 'J' then val := '1';
when chr = 'K' then val := '2';
when chr = 'L' then val := '3';
when chr = 'M' then val := '4';
when chr = 'N' then val := '5';
when chr = 'O' then val := '6';
when chr = 'P' then val := '7';
when chr = 'Q' then val := '8';
when chr = 'R' then val := '9';
when chr = 'S' then val := '0';
when chr = 'T' then val := '1';
when chr = 'U' then val := '2';
when chr = 'V' then val := '3';
when chr = 'W' then val := '4';
when chr = 'X' then val := '5';
when chr = 'Y' then val := '6';
when chr = 'Z' then val := '7';
else return false;
end case;
if mod(y,2) = 0 then
even:=even+val;
else
odd:=odd+val;
end if;
end loop;
tot:=(even*2)+odd; num:=tot;
while mod(num,10) != 0 loop
num:=num+1;
end loop;
chk := num - tot; if chk = 10 then chk := 0; end if;
if substr(trk,18,1)=(chk) then
return true;
end if;
end if;
end if;
return false;
exception
when others then
return false;
end;
