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