驗證 GST 辨識號 (GSTIN)

下圖描述了 GST 標識號的格式：

• 根據 2011 年印度人口普查，前 2 位數字表示唯一的州程式碼。例如，新德里的州程式碼是“07”，卡納塔克邦的州程式碼是“29”。
• 接下來的 10 個字元表示納稅人的PAN（永久帳號）。
• 第 13 位表示具有相同PAN的納稅人的註冊號（或實體號） 。
• 預設情況下，第 14 位數字是“Z”——目前不打算做任何事情。
• 第 15 位是校驗和數字- 可以是數字或字母字元。

也許我可以通過 PATINDEX 或 Regex 進行驗證？

為此，您不需要 PATINDEX 或 RegEx。

CREATE TABLE #floob
(
 GSTINColumn char(15),
 CONSTRAINT CheckGSTINColumn CHECK 
 (
   GSTINColumn  LIKE '[0-9][0-9][0-9A-Z]'
     + '[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'
     + '[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'
     + '[0-9A-Z][0-9]Z[0-9A-Z]'
 )
);

-- succeeds
INSERT #floob(GSTINColumn) VALUES('22AAAAA0000A1Z5');
GO
-- succeeds
INSERT #floob(GSTINColumn) VALUES('675T3E5600AZ7Z9');
GO
-- fails
INSERT #floob(GSTINColumn) VALUES('22AAAAA0000A1X5');
GO

DROP TABLE #floob;

為了補充 Aaron 的完全有效答案，您可能決定通過驗證校驗位的正確性來對 GSTIN 號碼進行更深入的驗證，校驗位顯示為數字中的最後一位數字。

下面的程式碼創建了許多函式，a 將使用這些函式CHECK CONSTRAINT來驗證新行以及對 GSTIN 編號的任何修改。

前兩個函式提供將 GSTIN 編號中包含的 ASCII 字元任意映射到 base-36；即0映射到0、9映射到9、A映射到10和Z映射到35：

IF OBJECT_ID(N'dbo.map_char', N'FN') IS NOT NULL
DROP FUNCTION dbo.map_char;
GO
CREATE FUNCTION dbo.map_char(@c char(1))
RETURNS int
AS
BEGIN
   DECLARE @val int;
   SET @c = UPPER(@c);
   IF ASCII(@c) >= 48 AND ASCII(@c) <= 57
       SET @val = ASCII(@c) - 48;
   IF ASCII(@c) >= 65 AND ASCII(@c) <= 90
       SET @val = (ASCII(@c) - 65) + 10;
   RETURN @val;
END
GO
IF OBJECT_ID(N'dbo.unmap_char', N'FN') IS NOT NULL
DROP FUNCTION dbo.unmap_char;
GO
CREATE FUNCTION dbo.unmap_char(@v int)
RETURNS char(1)
AS
BEGIN
   DECLARE @c char(1);
   IF @v >= 0 AND @v <=9 
       SET @c = CHAR(@v + 48);
   IF @v >= 10 AND @v <= 90
       SET @c = CHAR((@v + 65) - 10);
   RETURN @c;
END
GO

我無法確定印度政府是否使用相同的編碼，但它似乎適用於提供的值。

此程式碼使用上述dbo.map_char函式驗證給定 GSTIN 號碼中的校驗位。

CREATE FUNCTION dbo.fn_validate_gstin
(
   @inp char(15)
)
RETURNS tinyint
AS
BEGIN
   DECLARE @return tinyint;
   DECLARE @i int = LEN(@inp);
   DECLARE @factor int = 1;
   DECLARE @char char(1);
   DECLARE @codepoint int;
   DECLARE @addend int;
   DECLARE @sum int = 0;
   IF @i <> 15 /* GSTIN MUST be 15 characters to be valid */
   BEGIN
       SET @return = 0;
   END
   ELSE
   BEGIN
       WHILE @i > 0
       BEGIN
           SET @codepoint = dbo.map_char(SUBSTRING(@inp, @i, 1));
           SET @addend = @factor * @codepoint;
           SET @addend = (@addend / 36) + (@addend % 36);
           SET @sum += @addend;
           IF @factor = 2 SET @factor = 1 ELSE SET @factor = 2;
           SET @i -= 1;
       END
   END
   DECLARE @remainder int = @sum % 36;
   IF @remainder = 0 SET @return = 1 ELSE SET @return = 0;
   RETURN @return;
END
GO

請注意，此程式碼中絕對沒有錯誤檢查；我把它留給讀者作為練習。1如果 GSTIN 包含有效的校驗位作為最後一位，則該函式返回。如果校驗位無效，則返回 0。

在這裡，我創建了一個實現該dbo.fn_validate_gstin功能的表：

CREATE TABLE dbo.t
(
   i char(15) NOT NULL
       CONSTRAINT CK_t_valid_gstin
       CHECK (dbo.fn_validate_gstin(i) = 1)
);

在這裡，我們插入幾個“測試”GSTIN 號碼：

INSERT INTO dbo.t (i) VALUES ('123456789012345');
INSERT INTO dbo.t (i) VALUES ('27AAFFM5744C1ZE');
INSERT INTO dbo.t (i) VALUES ('27AAACE7932L1ZC');

第一次插入失敗。第二次和第三次插入成功。嘗試插入無效的 GSTIN 號碼會導致此錯誤：

消息 547，級別 16，狀態 0，第 80 行

INSERT 語句與 CHECK 約束“CK_t_valid_gstin”衝突。

衝突發生在數據庫“tempdb”、表“dbo.t”、列“i”中。

請注意，作為約束的一部分存在標量函式會阻止使用並行性的查詢。這對您的系統可能有問題，也可能沒有問題。如果您需要並行性，您可以考慮在表上使用INSTEAD OF觸發器來檢查插入或更新時的 GSTIN 編號。例如：

IF OBJECT_ID(N'dbo.t_with_trigger', N'U') IS NOT NULL
DROP TABLE dbo.t_with_trigger;
GO
CREATE TABLE dbo.t_with_trigger
(
   i char(15) NOT NULL
);

GO
CREATE TRIGGER t_validate
ON dbo.t_with_trigger
INSTEAD OF INSERT, UPDATE
AS
BEGIN
   SET NOCOUNT ON;
   BEGIN TRANSACTION
   DECLARE @bOk bit = 1;
   IF EXISTS (
       SELECT TOP(1) 1
       FROM inserted i
       WHERE dbo.fn_validate_gstin(i.i) = 0
       UNION ALL
       SELECT TOP(1) 1
       FROM deleted d
       WHERE dbo.fn_validate_gstin(d.i) = 0
       )
   BEGIN
       SET @bOk = 0; 
   END
   IF @bOk = 1 
   BEGIN
       DELETE
       FROM dbo.t_with_trigger
       FROM dbo.t_with_trigger t
           INNER JOIN deleted d ON t.i = d.i;
       INSERT INTO dbo.t_with_trigger(i)
       SELECT i.i
       FROM inserted i;
       COMMIT TRANSACTION;
   END
   ELSE
   BEGIN
       ROLLBACK TRANSACTION;
       DECLARE @msg varchar(1000);
       SET @msg = 'Attempted to insert/update using an invalid GSTIN number.';
       RAISERROR (@msg, 14, 1);
   END
END
GO

插入無效的 GSTIN 號碼：

INSERT INTO dbo.t_with_trigger (i) 
VALUES ('123456789012345')
   , ('27AAFFM5744C1ZE')
   , ('27AAACE7932L1ZC');

導致此錯誤：

消息 50000，級別 14，狀態 1，過程 t_validate，第 37 行

$$ Batch Start Line 129 $$

嘗試使用無效的 GSTIN 號碼插入/更新。

Msg 3609, Level 16, State 1, Line 130

事務在觸發器中結束。該批次已中止。

我的實現基於LUHN 算法。

引用自：https://dba.stackexchange.com/questions/203612