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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sergio Govoni » How to calculate the check digit for a...

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

Rate Topic

Display Mode

Topic Options

Author

Message

sgovoni

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

ray.mccoy

Posted Wednesday, July 09, 2008 5:56 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, August 12, 2008 6:44 AM
Points: 17, 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!

Post #530668

sgovoni

Posted Wednesday, July 09, 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
END

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

Thanks for your message!

Bye

Post #531119

CELKO

Posted Wednesday, August 29, 2012 12:18 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

I think this is the wrong appraoch. SQL is a data language. There ought to be a CHECK() in the DDL to protect data integrity. The non-relational UDF code cannot be optimized. The mindset here is so linked into procedural coding and not sets, that he starts the sring with a zero position, just like an array. He also writes with local varibles and loops!

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,
..);

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1351823

sgovoni

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

sgovoni

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_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

Post #1351964

« Prev Topic | Next Topic »

Permissions