Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Check Digit calc Expand / Collapse
Author
Message
Posted Wednesday, May 19, 2004 7:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 6, 2009 8:08 PM
Points: 72, Visits: 105

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




Post #116738
Posted Friday, May 21, 2004 4:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 1, 2014 4:19 AM
Points: 45, Visits: 111

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

 


 

 

Post #117003
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse