MySQL query to calculate EAN 13 checksum

The now called Global Trade Item Numbers (GTIN or GTIN 13), formerly called European Article Numbers (EAN or EAN 13) have 12 information digits and one extra for cheksum.

Using MySQL native SQL queries, you can calculate (query) the checksum number the following way:

Lets imagine a table product with a column named ean, the SQL returning the checksum for every ean row would be:

SELECT 

SUBSTRING((10 - ((((

SUBSTRING(ean FROM 2 FOR 1) +

SUBSTRING(ean FROM 4 FOR 1) +

SUBSTRING(ean FROM 6 FOR 1) +

SUBSTRING(ean FROM 8 FOR 1) +

SUBSTRING(ean FROM 10 FOR 1) +

SUBSTRING(ean FROM 12 FOR 1)  

)*3) + (

SUBSTRING(ean FROM 1 FOR 1) +

SUBSTRING(ean FROM 3 FOR 1) +

SUBSTRING(ean FROM 5 FOR 1) +

SUBSTRING(ean FROM 7 FOR 1) +

SUBSTRING(ean FROM 9 FOR 1) +

SUBSTRING(ean FROM 11 FOR 1)

)) MOD 10)) 

FROM -1 FOR 1) 

FROM product