European Article Number Check PlSql

1 - About

An EAN-13 barcode (originally European Article Number) is a barcoding standard which is a superset of the original 12-digit Universal Product Code (UPC) system developed in North America. The EAN-13 barcode is defined by the standards organisation GS1. The numbers encoded in EAN-13 bar codes are product identification numbers which are called Japanese Article Number (JAN) in Japan. All the numbers encoded in UPC and EAN barcodes are known as Global Trade Item Numbers (GTIN), and they can be encoded in other GS1 barcodes.

2 - Function Check

CREATE OR REPLACE FUNCTION QAS_DWH.EAN_CODE_CHECK(p_eancode VARCHAR2) RETURN VARCHAR2 IS
 
v_code   VARCHAR2(18) := NULL;
v_check  NUMBER       := 0;
v_total  NUMBER       := 0;
BEGIN
--
IF LENGTH(p_eancode) != 18 
   THEN RETURN 'NOK';
END IF;
v_code   := p_eancode;
v_check  := v_code;  -- numeriek check 
IF SUBSTR(v_code,1,2) != '87' 
   THEN RETURN 'NOK';
END IF;
IF v_code = '000000000000000000'
   THEN RETURN 'NOK';
END IF; 
v_total :=   (SUBSTR(v_code,1,1) * 3) + (SUBSTR(v_code,2,1) * 1) + (SUBSTR(v_code,3,1) * 3) +
             (SUBSTR(v_code,4,1) * 1) + (SUBSTR(v_code,5,1) * 3) + (SUBSTR(v_code,6,1) * 1) +
             (SUBSTR(v_code,7,1) * 3) + (SUBSTR(v_code,8,1) * 1) + (SUBSTR(v_code,9,1) * 3) +
             (SUBSTR(v_code,10,1) * 1) + (SUBSTR(v_code,11,1) * 3) + (SUBSTR(v_code,12,1) * 1) +
             (SUBSTR(v_code,13,1) * 3) + (SUBSTR(v_code,14,1) * 1) + (SUBSTR(v_code,15,1) * 3) +
             (SUBSTR(v_code,16,1) * 1) + (SUBSTR(v_code,17,1) * 3)
             ;
v_check  := MOD(v_total,10);
 
IF v_check = 0 THEN
   v_check := 10;
END IF;
IF (10 - v_check) != SUBSTR(v_code,18,1)  
   THEN RETURN 'NOK '||v_total;
END IF;
 
RETURN 'OK';
EXCEPTION 
   WHEN OTHERS THEN  
   RETURN 'NOK'; 
END;
/

3 - Reference

barcode_ean.txt ยท Last modified: 2009/05/06 09:31 by gerardnico