Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How to calculate the check digit for a UCC/EAN code Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, May 29, 2008 11:28 AM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, August 29, 2012 5:09 PM Points: 42, Visits: 194
 Comments posted to this topic are about the item How to calculate the check digit for a UCC/EAN code
Post #508572
 Posted Wednesday, July 9, 2008 5:56 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, August 12, 2008 6:44 AM Points: 17, Visits: 6
 declare @RES intexec @RES = dbo.UDF_GetCheckDigitUCCEAN '0000000001111'print @RESThis yields a value of -8, not a valid value check digit value! I figure it should be 2.Cheers!
Post #530668
 Posted Wednesday, July 9, 2008 11:40 AM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, August 29, 2012 5:09 PM Points: 42, Visits: 194
 Sorry, there was a small bug in step 5 (subtract the result obtained in paragraph 4. by multiple of 10 higher).The local variable @i must start from zero and not from one, the step 5 is amended as follows: /* 5. Subtract the result obtained in paragraph 4) by multiple of 10 higher. */ IF ((@CheckSum % 10) = 0) BEGIN -- Check digit equal ZERO SET @CheckSum = 0 END ELSE BEGIN SET @tmpMulSup = LTRIM(RTRIM(STR(@CheckSum))) --SET @i = 1 SET @i = 0 WHILE @i <= (LEN(@tmpMulSup) - 1) BEGIN SET @tmp = @tmp + SUBSTRING(@tmpMulSup, @i, 1) IF (@i = LEN(@tmpMulSup) - 1) BEGIN SET @tmp = LTRIM(RTRIM(STR(CAST(@tmp AS INTEGER) + 1))) SET @tmp = @tmp + '0' END SET @i = (@i + 1) END SET @CheckSum = CAST(@tmp AS INTEGER) - @CheckSum ENDNow, the check digit for a code UCC/EAN '0000000001111' is 2 :)Thanks for your message!Bye
Post #531119
 Posted Wednesday, August 29, 2012 2:55 PM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, August 29, 2012 5:09 PM Points: 42, Visits: 194
 Hi Celko,thanks for your message. The function is used to calculate a UCC/EAN check-digit and of course it will could be improved. It is a function for string manipulation.Thanks!Sergio
Post #1351917
 Posted Wednesday, August 29, 2012 5:09 PM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, August 29, 2012 5:09 PM Points: 42, Visits: 194
 Hi Celko,How about somethign like this:CREATE TABLE Produ ts(ean CHAR(13) NOT NULL PRIMARY KEY CONSTRAINT valid_eanCHECK(ean LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')CONSTRAINT good_check_digitCHECK (CAST SUBSTRING (ean, 13, 1) AS INTEGER)= (3* CAST SUBSTRING (ean, 1, 1) AS INTEGER)+ CAST SUBSTRING (ean, 2, 1) AS INTEGER)+ 3* CAST SUBSTRING (ean, 3, 1) AS INTEGER)+ CAST SUBSTRING (ean, 4, 1) AS INTEGER)+ 3* CAST SUBSTRING (ean, 5, 1) AS INTEGER)+ CAST SUBSTRING (ean, 6, 1) AS INTEGER)+ 3* CAST SUBSTRING (ean, 7, 1) AS INTEGER)+ CAST SUBSTRING (ean, 8, 1) AS INTEGER)+ 3* CAST SUBSTRING (ean, 9, 1) AS INTEGER)+ CAST SUBSTRING (ean, 10, 1) AS INTEGER)+ 3* CAST SUBSTRING (ean, 11, 1) AS INTEGER)+ CAST SUBSTRING (ean, 12, 1) AS INTEGER)+ 3* CAST SUBSTRING (ean, 13, 1) AS INTEGER)) % 10,..);based on your set-based solution, and for UCC/EAN 13 BC, I have modified the latter CHECK constraint because there was a little issue on the digit numbering.I think that this code is right:CREATE TABLE Produ ts(ean CHAR(13) NOT NULL PRIMARY KEY CONSTRAINT valid_eanCHECK(ean LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')CONSTRAINT good_check_digit CHECK(CAST(SUBSTRING(ean, 13, 1) AS INTEGER) = (10 - (3* CAST(SUBSTRING(('0'+ean), 1, 1) AS INTEGER) + CAST(SUBSTRING(('0'+ean), 2, 1) AS INTEGER) + 3* CAST(SUBSTRING(('0'+ean), 3, 1) AS INTEGER) + CAST(SUBSTRING(('0'+ean), 4, 1) AS INTEGER) + 3* CAST(SUBSTRING(('0'+ean), 5, 1) AS INTEGER) + CAST(SUBSTRING(('0'+ean), 6, 1) AS INTEGER) + 3* CAST(SUBSTRING(('0'+ean), 7, 1) AS INTEGER) + CAST(SUBSTRING(('0'+ean), 8, 1) AS INTEGER) + 3* CAST(SUBSTRING(('0'+ean), 9, 1) AS INTEGER) + CAST(SUBSTRING(('0'+ean), 10, 1) AS INTEGER) + 3* CAST(SUBSTRING(('0'+ean), 11, 1) AS INTEGER) + CAST(SUBSTRING(('0'+ean), 12, 1) AS INTEGER) + 3* CAST(SUBSTRING(('0'+ean), 13, 1) AS INTEGER) ) % 10)));For check the last digit on EAN 13 BC I used the check-digit calculator on INDICOD home site:http://indicod-ecr.it/servizi/codifica/calcolo-check-digit/ Thanks again!Sergio
Post #1351964
 Posted Tuesday, May 10, 2016 7:03 AM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, December 1, 2016 7:11 AM Points: 8,298, Visits: 852
 Thanks for the script.
Post #1784711

 Permissions