I have found occasion to need a way in SQL server to validate and/or calculate UPCa and EAN-8 check digits. I wrote a few UDFs in SQL Server 2000 to accomplish this. I also wrote one for EAN-13 since it was so similar to UPCa, but haven't tested/needed it yet.
I will include the functions here in case anyone wants to improve upon them. Probably a little logic could be added to combine some of them into a single function. Possibly perfomance could be increased by loading the characters of the string into an array to perform the validatation and calculations. These functions currently accept the UPC codes with or without the check digit. It ignores it either way, so input == output if the check digit is correct.
--This Function takes an 11 or 12 character input and outputs the full UPCa with correct check digit calculated.
--Possible improvements include checking input string for '[0-9]' input only, and perhaps placing input string into and integer array.
--Also, this could perhaps be modified to work for EAN-8 and maybe even EAN-13 as well.
CREATE FUNCTION [dbo].[GenerateFullUPCa] (@InputUPC nchar(20))
RETURNS nchar(12)
BEGIN
DECLARE @Sum int
DECLARE @ChkDgt nchar(1)
IF ( (Len(@InputUPC) 12) )
BEGIN
RETURN NULL
END
ELSE IF (Len(@InputUPC) = 12)
BEGIN
SET @InputUPC = Left(@InputUPC, 11)
END
SET @Sum = 3 * ( CAST(Substring(@InputUPC,1,1) AS int) + CAST(Substring(@InputUPC,3,1) AS int) + CAST(Substring(@InputUPC,5,1) AS int) + CAST(Substring(@InputUPC,7,1) AS int) + CAST(Substring(@InputUPC,9,1) AS int) + CAST(Substring(@InputUPC,11,1) AS int) ) + CAST(Substring(@InputUPC,2,1) AS int) + CAST(Substring(@InputUPC,4,1) AS int) + CAST(Substring(@InputUPC,6,1) AS int) + CAST(Substring(@InputUPC,8,1) AS int) + CAST(Substring(@InputUPC,10,1) AS int)
IF (@Sum % 10) = 0 BEGIN SET @ChkDgt = '0' END
ELSE IF (@Sum % 10) = 1 BEGIN SET @ChkDgt = '9' END
ELSE IF (@Sum % 10) = 2 BEGIN SET @ChkDgt = '8' END
ELSE IF (@Sum % 10) = 3 BEGIN SET @ChkDgt = '7' END
ELSE IF (@Sum % 10) = 4 BEGIN SET @ChkDgt = '6' END
ELSE IF (@Sum % 10) = 5 BEGIN SET @ChkDgt = '5' END
ELSE IF (@Sum % 10) = 6 BEGIN SET @ChkDgt = '4' END
ELSE IF (@Sum % 10) = 7 BEGIN SET @ChkDgt = '3' END
ELSE IF (@Sum % 10) = 8 BEGIN SET @ChkDgt = '2' END
ELSE IF (@Sum % 10) = 9 BEGIN SET @ChkDgt = '1' END
ELSE BEGIN SET @ChkDgt = 'Z' END
RETURN Left(@InputUPC,11) + @ChkDgt
END
--This Function takes a 7 or 8 character input and outputs the full EAN-8 with correct check digit calculated.
--Possible improvements include checking input string for '[0-9]' input only, and perhaps placing input string into and integer array.
--Also, this could perhaps be integrated with UPCa and maybe even EAN-13 functions as well.
CREATE FUNCTION [dbo].[GenerateFullEAN8] (@InputUPC nchar(20))
RETURNS nchar(8)
BEGIN
DECLARE @Sum int
DECLARE @ChkDgt nchar(1)
IF ( (Len(@InputUPC) 8) )
BEGIN
RETURN NULL
END
ELSE IF (Len(@InputUPC) = 8)
BEGIN
SET @InputUPC = Left(@InputUPC, 7)
END
SET @Sum = 3 * ( CAST(Substring(@InputUPC,1,1) AS int) + CAST(Substring(@InputUPC,3,1) AS int) + CAST(Substring(@InputUPC,5,1) AS int) + CAST(Substring(@InputUPC,7,1) AS int) ) + CAST(Substring(@InputUPC,2,1) AS int) + CAST(Substring(@InputUPC,4,1) AS int) + CAST(Substring(@InputUPC,6,1) AS int)
IF (@Sum % 10) = 0 BEGIN SET @ChkDgt = '0' END
ELSE IF (@Sum % 10) = 1 BEGIN SET @ChkDgt = '9' END
ELSE IF (@Sum % 10) = 2 BEGIN SET @ChkDgt = '8' END
ELSE IF (@Sum % 10) = 3 BEGIN SET @ChkDgt = '7' END
ELSE IF (@Sum % 10) = 4 BEGIN SET @ChkDgt = '6' END
ELSE IF (@Sum % 10) = 5 BEGIN SET @ChkDgt = '5' END
ELSE IF (@Sum % 10) = 6 BEGIN SET @ChkDgt = '4' END
ELSE IF (@Sum % 10) = 7 BEGIN SET @ChkDgt = '3' END
ELSE IF (@Sum % 10) = 8 BEGIN SET @ChkDgt = '2' END
ELSE IF (@Sum % 10) = 9 BEGIN SET @ChkDgt = '1' END
ELSE BEGIN SET @ChkDgt = 'Z' END
RETURN Left(@InputUPC,7) + @ChkDgt
END
--This Function takes a 12 or 13 character input and outputs the full EAN-13 UPC with correct check digit calculated.
--Possible improvements include checking input string for '[0-9]' input only, and perhaps placing input string into and integer array.
--Also, this could perhaps be modified to work for UPCa and EAN-8 functions.
CREATE FUNCTION [dbo].[GenerateFullEAN13] (@InputUPC nchar(20))
RETURNS nchar(13)
BEGIN
DECLARE @Sum int
DECLARE @ChkDgt nchar(1)
IF ( (Len(@InputUPC) 13) )
BEGIN
RETURN NULL
END
ELSE IF (Len(@InputUPC) = 13)
BEGIN
SET @InputUPC = Left(@InputUPC, 12)
END
SET @Sum = 3 * ( CAST(Substring(@InputUPC,2,1) AS int) + CAST(Substring(@InputUPC,4,1) AS int) + CAST(Substring(@InputUPC,6,1) AS int) + CAST(Substring(@InputUPC,8,1) AS int) + CAST(Substring(@InputUPC,10,1) AS int) + CAST(Substring(@InputUPC,12,1) AS int) ) + CAST(Substring(@InputUPC,1,1) AS int) + CAST(Substring(@InputUPC,3,1) AS int) + CAST(Substring(@InputUPC,5,1) AS int) + CAST(Substring(@InputUPC,7,1) AS int) + CAST(Substring(@InputUPC,9,1) AS int) + CAST(Substring(@InputUPC,11,1) AS int)
IF (@Sum % 10) = 0 BEGIN SET @ChkDgt = '0' END
ELSE IF (@Sum % 10) = 1 BEGIN SET @ChkDgt = '9' END
ELSE IF (@Sum % 10) = 2 BEGIN SET @ChkDgt = '8' END
ELSE IF (@Sum % 10) = 3 BEGIN SET @ChkDgt = '7' END
ELSE IF (@Sum % 10) = 4 BEGIN SET @ChkDgt = '6' END
ELSE IF (@Sum % 10) = 5 BEGIN SET @ChkDgt = '5' END
ELSE IF (@Sum % 10) = 6 BEGIN SET @ChkDgt = '4' END
ELSE IF (@Sum % 10) = 7 BEGIN SET @ChkDgt = '3' END
ELSE IF (@Sum % 10) = 8 BEGIN SET @ChkDgt = '2' END
ELSE IF (@Sum % 10) = 9 BEGIN SET @ChkDgt = '1' END
ELSE BEGIN SET @ChkDgt = 'Z' END
RETURN Left(@InputUPC,12) + @ChkDgt
END