MySQL Data Cleanse EAN Numbers
November 17th, 2009Read More barcode
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).
SQL:
-
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))
