MySQL Data Cleanse EAN Numbers

Nov 17, 2009 · 1 minute read
Category: barcode

This is post is now quite old and the the information it contains may be out of date or innacurate.

If you find any errors or have any suggestions to update the information please let us know or create a pull request on GitHub

Just found this MySQL snippet for validating EAN numbers.

http://snipplr.com/view.php?codeview&id=17928

I have modified it a bit to suit my requirements (namely totally corrupted EAN data).

SELECT ean
FROM products
WHERE
(LENGTH(ean) != 13)
||
(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) != SUBSTRING(ean FROM 13 FOR 1))