SQL proc to calculate check digit for 7 and 12 digit upc

Related searches

I just had to scour the internet for this code yet again so I figured I would put it here so I can find it a little faster the next time and hopefully you found it a little faster too :)


Check this. The code below can check digit in all GTIN's (EAN8, EAN13, EAN14, UPC/A, UPC/E)

CREATE FUNCTION [dbo].[check_digit]
(
    @GTIN VARCHAR(14)
)
RETURNS TINYINT
AS
BEGIN
    DECLARE @Index TINYINT,
            @Multiplier TINYINT,
            @Sum TINYINT,
            @checksum TINYINT,
            @result TINYINT,
            @GTIN_strip VARCHAR(13)

    SELECT  @GTIN_strip = SUBSTRING(@GTIN,1,LEN(@GTIN)-1);

    SELECT  @Index = LEN(@GTIN_strip),
            @Multiplier = 3,
            @Sum = 0

    WHILE @Index > 0
        SELECT  @Sum = @Sum + @Multiplier * CAST(SUBSTRING(@GTIN_strip, @Index, 1) AS TINYINT),
            @Multiplier = 4 - @Multiplier,
            @Index = @Index - 1

    SELECT @checksum = CASE @Sum % 10
            WHEN 0 THEN '0'
            ELSE CAST(10 - @Sum % 10 AS CHAR(1))
        END


    IF (SUBSTRING(@GTIN,LEN(@GTIN),1) = @checksum)
        RETURN 1; /*true*/
        RETURN 0; /*false*/
END

Calculating and Verifying Check Digits in T-SQL, Calculating the Check Digit for a Universal Product Code (UPC) 7. Number the digits: 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. Multiplier: 3. 1. 3. 1. 3. 1. 3 Msg 3729, Level 16, State 3, Procedure CalculateCheckDigitUPC, Line 8. Check Digit Calculator. Instructions. Enter a product code WITHOUT ITS CHECK DIGIT! DO NOT omit leading zeros. This will be 12 digits for an EAN, JAN, or UCC-13, 11 digits for a UPC-A, or 7 digits for a UPC-E. There is no support here for EAN/UCC-8 yet.


CREATE FUNCTION [dbo].[fn_EAN13CheckDigit] (@Barcode nvarchar(12)) 
RETURNS nvarchar(13) 
AS 
BEGIN 
    DECLARE @SUM int , @COUNTER int, @RETURN varchar(13), @Val1 int, @Val2 int 
    SET @COUNTER = 1 SET @SUM = 0 

    WHILE @Counter < 13 
    BEGIN 
        SET @VAL1 = SUBSTRING(@Barcode,@counter,1) * 1 
        SET @VAL2 = SUBSTRING(@Barcode,@counter + 1,1) * 3 
        SET @SUM = @VAL1 + @SUM 
        SET @SUM = @VAL2 + @SUM 
        SET @Counter = @Counter + 2; 
    END 
    SET @SUM=(10-(@SUM%10))%10

    SET @Return = @BARCODE + CONVERT(varchar,((@SUM))) 
    RETURN @Return 
END

Calculate Upc Code Check Digit - 08/2020, Check Digit Calculator for UPC Code and Shipping Container Free www. upccode.net SQL proc to calculate check digit for 7 and 12 digit upc. Ask Question� Check Digit calc – Learn more on the SQLServerCentral forums to calculate check digits, this is for standard food product upc codes (gtn compliant) It checks to see if the checksum digit


And here's a MySQL implementation as well. Note it requires a 13 digit UPC/EAN13 as input, so you should LPAD your input as required.

drop function if exists barcodify;
delimiter //
 at the last digit.
create function barcodify(upc varchar(15))
    returns varchar(15)
    sql security invoker
begin
    declare i, r, odd, even,result int;

    set odd=0;
    set even =0;
    set i = length(upc);

    while i > 0 do
        set r = substring(upc, i, 1) ;
        if(i % 2 >0) then set odd = odd + r;
        else set even = even + r;
        end if;
        set i = i - 1;
    end while;
    set result = (3*odd)+even;

    if result % 10 =0 then return 0;
    else return (10 - (result %10));
    end if;
end //

delimiter ;

Generate UPC codes with SQL (I just need syntax), What I need is the 12th digit called the "Check Digit" of the UPC. Its calculated Even placed numbers: 7 + 2 + 1 + 1 + 3 = 14 The next part of the function in SQL I need is something to calculate this Check Digit. CREATE PROCEDURE genCheckDigit(@strUPC varchar(11), @checkDigit int output) AS In general, check digits are a single digit computed from the other characters in the string of numbers. The digit on the far right of a UPC barcode is the UPC check digit. All UPC (Universal Product Code) barcodes include a modulo-10 check digit. The UPC check digit is the twelfth digit, located to the far right of the UPC number.


Here's my implementation of a function in Oracle.

CREATE OR REPLACE FUNCTION GenerateCheckDigit(pUPC IN VARCHAR2) RETURN INT
  IS
  vCheckDigit INT;
  BEGIN

    WITH barcodeData AS 
    (
      SELECT pUPC barcode FROM dual
    )
    SELECT       
      10-REPLACE(MOD(SUM(SUBSTR(barcode, -LEVEL, 1) * DECODE(MOD(LEVEL-1, 2), 1, 1, 3)),10),0,10) INTO vCheckDigit
      FROM barcodeData 
    CONNECT BY LEVEL <= LENGTH(barcode);

    RETURN vCheckDigit;

  END;

Check Digits 101: How do you code a Check Digit algorithm for , Then an algorithm is applied to the number to get the check digit. which contains the definition for a PL/SQL procedure that defines this 1) Sum the digits 1, 3, 5, 7, and 9. Rewrite ONLY the portion of the code where the comment is below to calculate the check digit according to the new algorithm. I'm trying to create a check digit for 13 bytes of data for the human readable portion of a 128 B barcode within my program. 12:37 PM. Re: Check Digit calculation


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) RETURN NULL

    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

Check digit calculator, GS1 Check Digit Calculator can calculate the last digit of a barcode number, making sure the barcode is correctly composed. Calculate a check digit. Teams. Q&A for Work. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information.


The check digit is the number which adds the remainder to 10. In our case, divide 85 by 10 we get the remainder 5. The check digit then is the result of 10-5=5. So, what I did to calculate this was like that: 1. Cell A1 is the eleven digit number: 72641217542 2. Cells A2 to A12 are 7, 2, 6, 4, 1, 2, 1, 7 ,5, 4, 2 correspondingly. 3.


The number without the check digit is 501001708843. Number the digits 1 through 12, counting “3” as the first digit and “5” as the last, in this example. Add the odd digits: 3 + 8 + 0 + 1 + 0 + 0 = 12. Multiply the answer by 3: 12 x 3 = 36. Add the digits in even positions: 4 + 8 + 7 + 0 + 1 + 5 = 25.


All GS1 ID Keys need a check digit, except Component/Part Identifier (CPID), Global Individual Asset Identifier (GIAI) and Global Model Number (GMN). How does the check digit calculator work? The check digit is calculated using a simple algorithm, based on the other numbers in the key. Find out how to calculate a check digit manually.