在 SQL Server 中计算 GS1 校验位

问题描述

如何计算 GTIN 的 GS1 校验位和 SQL Server 中的 SSCC 代码.

How to calculate a GS1 check digit for GTIN and SSCC codes in SQL Server.

推荐答案

这个用户定义的函数将计算 GS1 网站上所有提到的 GTIN 和 SSCC 格式的校验位.该函数将返回包含校验位作为最后一个字符的代码.

This User-Defined Function will calculate check digits for all of the mentioned GTIN and SSCC formats on the GS1 website. The function will return the code that includes the check digit as a last character.

CREATE FUNCTION [GtinCheckDigit] (@Input VARCHAR(17))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH [ReverseInput](S) AS (
    SELECT REVERSE(@Input)
), [CharCount](N) AS (
    SELECT n from (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17)) a(n)
), [CharPos](N,S) AS (
    SELECT TOP (LEN(@Input)) [CharCount].N,SUBSTRING([ReverseInput].S,[CharCount].N,1)
    FROM [CharCount],[ReverseInput]
), [Multiplier](N) AS (
    SELECT (S*CASE WHEN (N%2) = 0 THEN 1 ELSE 3 END)
    FROM [CharPos]
), [Checksum](N) AS (
    SELECT CASE WHEN (SUM(N)%10) > 0 THEN (10-(SUM(N)%10)) ELSE 0 END
    FROM [Multiplier]
)
SELECT @Input + CAST(N as VARCHAR) as [Output] from [Checksum];

如果您只需要检索计算出的校验位，您可以将函数的最后一行更改为如下所示:

If you only need to retreive the calculated check digit you can change the last line of the function to something like this:

SELECT N from [Checksum];

此函数仅适用于 SQL-Server 2008 或更高版本，因为 REVERSE 函数用于反转输入.

This function will only work on SQL-Server 2008 or higher because of the REVERSE function that is being used to reverse the input.