Oracle SQL EAN-13 check digit

When working with products in your company it is of importance that in your database you have the ability to uniquely identify a product by a product ID. A lot of companies do maintain a product ID specially used within that company. The products ID's are used in warehousing, sales, purchasing and finance. Meaning, the ID is used for a wide range of actions in your systems and  business processes. What you see is that a lot of companies do maintain their own product ID however link them to the standard product numbering system EAN. Reason for this is that EAN numbers are used across companies to identify a product uniquely.

An EAN-13 barcode (originally European Article Number, but now renamed International Article Number even though the abbreviation EAN has been retained) is a 13 digit (12 data and 1 check) barcoding standard which is a superset of the original 12-digit Universal Product Code (UPC) system developed in the United States. The EAN-13 barcode is defined by the standards organization GS1.

The EAN-13 barcodes are used worldwide for marking products often sold at retail point of sale. The numbers encoded in EAN-13 bar codes are product identification numbers, which are also 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.

The less commonly used EAN-8 barcodes are used also for marking retail goods; however, they are usually reserved for smaller items, for example confectionery.

2-digit (EAN 2) and 5-digit (EAN 5) supplemental barcodes may be added for a total of 14 or 17 data digits. These are generally used for periodicals (to indicate the current year's issue number), or books and weighed products like food (to indicate the manufacturer suggested retail price or MSRP), respectively.

Making sure that the EAN number you entered is a correct EAN number is vital to ensure that your database will only hold valid EAN numbers and no mistakes are made during the process of entering the number. The EAN numbering holds a mechanism to check if the number is valid. A EAN-13 number consists out of 12 data and 1 check digits. You can use the check digit to verify that the number entered is a valid EAN-13 number by using the check-digit algorithm.

To do the check you have to do a "matrix" calculation on the data digits from the EAN-13 number which are the first 12 digits. In this example we take the EAN-13 number for a MacBook Air which is 0885909592449.

Every position in the EAN-13 number has a weight associated with it, either a 1 or a 3. You will have to multiply every value of the position with the weight of the position and sum all the results up to one value.

In our case this is the final value 111. Take the nearest (upper) multiple of 10 that is equal or higher than the sum, the sum is in our case 111, meaning the nearest (upper) multiple of 10 is 120. Substract the sum from the nearest (upper) multiple of 10 and this should be equal to your check digit which in our case is 9. 120 - 111 = 9. This means your EAN-13 number you have entered is a valid EAN-13 number.

Now that we understand this logic there might be the need to incorporate this into your database so you can use it as a check. Thanks to Asif Momen and the discussion on forums.oracle.com you can use the function below to do so:

CREATE OR REPLACE
  FUNCTION ean_13(
      P_Str IN VARCHAR2)
    RETURN VARCHAR2
  IS
    l_Sum      NUMBER;
    l_Multiple NUMBER;
  BEGIN
    FOR I IN 1..12
    LOOP
      IF mod(i,2)   = 0 THEN
        l_Multiple := 3;
      ELSE
        l_Multiple := 1;
      END IF;
      l_Sum := NVL(l_Sum, 0) + SUBSTR(P_Str, i, 1) * l_Multiple;
    END LOOP;
    IF 10 - Mod(l_Sum, 10) = SUBSTR(P_Str, 13) THEN
      RETURN('TRUE');
    ELSE
      RETURN('FALSE');
    END IF;
  END;