May 19, 2004 at 7:00 pm
Hi all i'm looking for a script (udf) to calculate check digits, this is for standard food product upc codes (gtn compliant)
Thanks
Richard
May 21, 2004 at 4:02 am
Hi,
Europe most uses EAN format code which are similar enough to UPC. Here's one I wrote earlier...
It checks to see if the checksum digit is valid.
/*check for UPC-A style barcode.
Return code
=>0 is OK
0 checkdigit ok)
1 price embedded
<0 invalid barcode
-1 invalid check digit
-2 not a number
-3 contains a .
*/
CREATE FUNCTION [dbo].[ChecksumUPCA] (@checkSum varchar(20))
RETURNS int AS
BEGIN
declare @isAbadBarcode int,@isAgoodBarcode int, @isNotANumber int, @hasAfullStop int
declare @isPEB int
declare @retVal int
declare @evenSum int, @oddSum int, @totalSum float
declare @PEB varchar(30)
set @PEB='02 20 21 23 28 29 27'
set @isAbadBarcode=-1
set @isAgoodBarcode=0
set @isNotANumber=-2
set @hasAfullStop=-3
set @isPEB=1
set @checksum=ltrim(rtrim(@checkSum))
set @retval=-1
--check for PEBs (Price Embedded Barcode)
/*if charindex(left(@checksum,2),@PEB)>0
begin
set @retval=@isPEB
goto quit
end
*/
if ISNUMERIC(@checkSum)=0
begin
set @retVal=@isNotANumber
goto quit
end
if charindex('.',@checkSum,1)>0
begin
set @retVal=@hasAfullStop
goto quit
end
set @evenSum=substring(@checkSum,2,1)
set @evenSum=@evenSum + substring(@checkSum,4,1)
set @evenSum=@evenSum + substring(@checkSum,6,1)
set @evenSum=@evenSum + substring(@checkSum,8,1)
set @evenSum=@evenSum + substring(@checkSum,10,1)
set @oddSum=substring(@checkSum,1,1)
set @oddSum=@oddSum + substring(@checkSum,3,1)
set @oddSum=@oddSum + substring(@checkSum,5,1)
set @oddSum=@oddSum + substring(@checkSum,7,1)
set @oddSum=@oddSum + substring(@checkSum,9,1)
set @oddSum=@oddSum + substring(@checkSum,11,1)
set @oddSum=@OddSum * 3
set @totalSum=@evenSum + @oddSum
set @totalSum=(ceiling(@totalSum/10)*10)-@totalSum
if @totalSum=right(@checkSum,1)
set @retval=@isAgoodBarcode
else
set @retval=@isAbadBarcode
quit:
return @retval
END
...and here's one an ancestral DBA left behind for me (never used it myself)
CREATE FUNCTION [dbo].[UPCE2UPCA] (@barcode varchar(6))
RETURNS varchar (12) AS
BEGIN
declare @retVal varchar(12)
declare @aNo tinyint
set @aNo= right(@barcode,1)
if @aNo>4
begin
set @retVal='0' + left(@barcode,5) + '0000'
set @retVal= @retVal + right(@barcode,1)
goto quit
end
if @aNo=4
begin
set @retVal='0' + left(@barcode,4) + '00000' + substring(@barcode,5,1)
goto quit
end
if @aNo=3
begin
set @retval='0' + left(@barcode,3) + '00000' + substring(@barcode,4,2)
goto quit
end
set @retval='0' + left(@barcode,2) + right(@barcode,1) + '0000' + substring(@barcode,3,3)
quit:
return @retVal
END
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply