May 29, 2008 at 11:28 am
Comments posted to this topic are about the item How to calculate the check digit for a UCC/EAN code
July 9, 2008 at 5:56 am
declare @RES int
exec @RES = dbo.UDF_GetCheckDigitUCCEAN '0000000001111'
print @RES
This yields a value of -8, not a valid value check digit value! I figure it should be 2.
Cheers!
July 9, 2008 at 11:40 am
Sorry, there was a small bug in step 5 (subtract the result obtained in paragraph 4. by multiple of 10 higher).
The local variable [font="Courier New"]@i[/font] must start from zero and not from one, the step 5 is amended as follows:
[font="Courier New"] /* 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)))
END
SET @i = (@i + 1)
END
SET @checksum = CAST(@tmp AS INTEGER) - @checksum
END[/font]
Now, the check digit for a code UCC/EAN '0000000001111' is 2 🙂
Thanks for your message!
Bye
August 29, 2012 at 2:55 pm
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
August 29, 2012 at 5:09 pm
Hi Celko,
How about somethign like this:
CREATE TABLE Produ ts
(ean CHAR(13) NOT NULL PRIMARY KEY
CONSTRAINT valid_ean
CHECK(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)
= (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_ean
CHECK(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
May 10, 2016 at 7:03 am
Thanks for the script.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply