問題描述

如何計算 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.

這篇關于在 SQL Server 中計算 GS1 校驗位的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！