Wednesday, March 10, 2010

Calculating EAN 8 / EAN 13 Check Digit

Just wrote a quick function to calculate the check digit for EAN 8 / EAN 13

 


CREATE  FUNCTION sfn_ean_chkdigit
(    
    @barcode    varchar(20)
)
RETURNS CHAR(1)
AS
BEGIN
    DECLARE
        @chk_digit    int,
        @chk        int
 
    DECLARE    @num TABLE
    (
        num    int
    )
 
    IF    LEN(@barcode) NOT IN (7, 12)
    BEGIN
        RETURN     NULL
    END
 
    INSERT INTO @num 
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL  SELECT  5 UNION ALL SELECT  6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
 
    SELECT    @chk_digit = SUM(CONVERT(int, SUBSTRING(@barcode, LEN(@barcode) - num + 1, 1)) * CASE WHEN num % 2 = 1 THEN 3 ELSE 1 END)
    FROM    @num
    WHERE    num    <= LEN(@barcode)
 
    SELECT    @chk_digit = (10 - (@chk_digit % 10)) % 10
 
     RETURN  CHAR(ASCII('0') + @chk_digit)
END

9 comments:

  1. This is GREAT! Thank You!

    ReplyDelete
  2. Muito Obrigado!!
    (Thanks a lot!!)

    ReplyDelete
  3. Perfect !!!
    Simple and efficient

    ReplyDelete
  4. Thanks, it help me a lot. I used it for Ingres-DB on a table with the field bc1. Now i can check all barcodes in one table with one sql statement. Here the sql :
    select bc1, org_chksum=int( right(bc1,1)) , chksum
    from(
    select bc1, chksum= mod( 10 - mod( SUM(cast( substr( bc1,12 - num + 1, 1) as int) * CASE WHEN mod(num , 2) = 1 THEN 3 ELSE 1 END) ,10),10)
    from (SELECT num=1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) num,
    table_with_ean1_field
    group by bc1
    )
    a
    where int( right(bc1,1)) != chksum /* give me rows, where the cecksum in field bc1 is wrong */

    ReplyDelete
  5. The UNION ALL part is ugly. here is my improvement idea...

    DECLARE @cnt INT = 1
    WHILE (@cnt <=12)
    BEGIN
    INSERT INTO @num SELECT @cnt
    SET @cnt = @cnt + 1
    END

    ReplyDelete