Our blog

 

MySQL Data Cleanse EAN Numbers

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

PLAIN TEXT
SQL:
  1. SELECT ean
  2. FROM products
  3. WHERE
  4. (LENGTH(ean) != 13)
  5. ||
  6. (SUBSTRING((10 - ((((
  7. SUBSTRING(ean FROM 2 FOR 1) +
  8. SUBSTRING(ean FROM 4 FOR 1) +
  9. SUBSTRING(ean FROM 6 FOR 1) +
  10. SUBSTRING(ean FROM 8 FOR 1) +
  11. SUBSTRING(ean FROM 10 FOR 1) +
  12. SUBSTRING(ean FROM 12 FOR 1) 
  13. )*3) + (
  14. SUBSTRING(ean FROM 1 FOR 1) +
  15. SUBSTRING(ean FROM 3 FOR 1) +
  16. SUBSTRING(ean FROM 5 FOR 1) +
  17. SUBSTRING(ean FROM 7 FOR 1) +
  18. SUBSTRING(ean FROM 9 FOR 1) +
  19. SUBSTRING(ean FROM 11 FOR 1)
  20. )) MOD 10)) FROM -1 FOR 1) != SUBSTRING(ean FROM 13 FOR 1))

More Reading:

 

Leave a Reply