A union of curiosity and data science

Knowledgebase and brain dump of a database engineer


Create UPC Check Digit Function

 

 
 
The function returns a UPC with the check digit when provided with the prefix numbers.
 
 

 

create function f_get_upc_check_digit (@upc_prefix varchar(32))

returns varchar(32) -- returns the whole upc.

as

 

begin

       -- http://www.gs1.org/barcodes/support/check_digit_calculator

 

       declare @base_index int

       set @base_index = len(@upc_prefix)

 

       declare @index int, @multiplier_start int, @sum int

       set @index = 1

       set @sum = 0

       set @multiplier_start = case when @base_index = 12 then 1 else 3 end

 

       while @index <= @base_index

       begin

                     set @sum = @sum + cast(substring(@upc_prefix, @index, 1) as int) * @multiplier_start

                     set @index = @index + 1

                     set @multiplier_start = case when @multiplier_start = 1 then 3 else 1 end

 

       end

 

       declare @calc_mod int, @check_digit int

       set @calc_mod = @sum%10

       set @check_digit = case when @calc_mod = 0 then 0 else -1 end

 

       while @calc_mod <> 0

       begin

              set @check_digit = @check_digit + 1     

              set @calc_mod = @sum%10

              set @sum = @sum + 1

       end

 

       return @upc_prefix + cast(@check_digit as varchar(64))

 

end

 

go

 

 
 

Test: Actual GTIN-12 UPCs

--886792630591

print dbo.f_get_upc_check_digit ('88679263059')

--886792630522

print dbo.f_get_upc_check_digit ('88679263052')

--886792630553

print dbo.f_get_upc_check_digit ('88679263055')

--886792630584

print dbo.f_get_upc_check_digit ('88679263058')

--886792630515

print dbo.f_get_upc_check_digit ('88679263051')

--886792630546

print dbo.f_get_upc_check_digit ('88679263054')

--886792630577

print dbo.f_get_upc_check_digit ('88679263057')

--886792635268

print dbo.f_get_upc_check_digit ('88679263526')

--886792630539

print dbo.f_get_upc_check_digit ('88679263053')

--886792630560

print dbo.f_get_upc_check_digit ('88679263056')
 
 
Result : 
 
 
 
 
 
Manual Check Digit Check. 
 
 
 
 
 

Add comment