SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to calculate the check digit for a UCC/EAN code


How to calculate the check digit for a UCC/EAN code

Author
Message
sgovoni
sgovoni
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 194
Comments posted to this topic are about the item How to calculate the check digit for a UCC/EAN code
ray.mccoy
ray.mccoy
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 6
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!
sgovoni
sgovoni
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 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
END


Now, the check digit for a code UCC/EAN '0000000001111' is 2 Smile

Thanks for your message!

Bye
sgovoni
sgovoni
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 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
sgovoni
sgovoni
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 194
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
Iwas Bornready
Iwas Bornready
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29502 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search