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.