SQL PROC计算校验位的7和12位UPC(SQL proc to calculate check

2019-10-19 05:46发布

我不得不再次冲刷这段代码的互联网,所以我想我会放在这里,所以我能找到它快一点,下一次,希望你发现它快一点太:)

Answer 1:

请确认。 下面的代码在所有的校验位的GTIN的(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


Answer 2:

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


Answer 3:

而且这里有一个MySQL的执行情况以及。 注意它需要一个13位UPC / EAN13作为输入,从而需要你应该LPAD输入。

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 ;


Answer 4:

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


文章来源: SQL proc to calculate check digit for 7 and 12 digit upc