T-SQL 實作 18 位身分證字號檢查碼驗證演算法

身分證字號檢查碼規則 (GB 11643-1999)

1. 前 17 位數字分別乘以加權因子：
   第 i 位因子 = 2^(18-i) mod 11
   得到因子序列：7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2
2. 將 17 位數字與對應因子相乘並求和。
3. 和除以 11 取餘數 (0~10)。
4. 餘數對應檢查碼：
   餘數: 0 1 2 3 4 5 6 7 8 9 10
檢查碼: 1 0 X 9 8 7 6 5 4 3 2

T-SQL 驗證函數 (內嵌表值函數，高效能)

CREATE FUNCTION dbo.fn_ValidateIDCard18 (@IDCard CHAR(18))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 
    CASE 
        WHEN @IDCard NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9X]' THEN 0
        ELSE
            CASE 
                WHEN SUBSTRING(@IDCard, 18, 1) = 
                    CASE (SELECT SUM(CAST(SUBSTRING(@IDCard, n, 1) AS INT) * w) 
                          FROM (VALUES 
                              (1,7),(2,9),(3,10),(4,5),(5,8),(6,4),(7,2),(8,1),
                              (9,6),(10,3),(11,7),(12,9),(13,10),(14,5),(15,8),(16,4),(17,2)
                          ) AS t(n,w)) % 11
                    THEN 1 ELSE 0 END
    END AS IsValid;
GO

使用範例

-- 驗證單一身分證
SELECT * FROM dbo.fn_ValidateIDCard18('34052419800101001X');  -- 回傳 1 表示合法

-- 批次驗證資料表中身分證欄位
SELECT id, idcard, v.IsValid
FROM Customer
CROSS APPLY dbo.fn_ValidateIDCard18(idcard) v
WHERE v.IsValid = 0;  -- 找出不合法資料

邏輯說明

• 使用 VALUES 建構因子表，避免硬編碼字串解析，效能佳。
• CROSS APPLY 逐列調用函數，適合集合式操作。
• 函數回傳 BIT (0/1)，直接可用於 WHERE、CHECK CONSTRAINT 或 CASE 運算式。

擴充：加入出生日期與性別解析

CREATE FUNCTION dbo.fn_ParseIDCard18 (@IDCard CHAR(18))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT 
    CASE WHEN v.IsValid = 1 THEN 
        TRY_CAST(SUBSTRING(@IDCard, 7, 8) AS DATE) 
    END AS BirthDate,
    CASE WHEN v.IsValid = 1 THEN 
        CASE CAST(SUBSTRING(@IDCard, 17, 1) AS INT) % 2 WHEN 1 THEN 'M' ELSE 'F' END
    END AS Gender,
    v.IsValid
FROM dbo.fn_ValidateIDCard18(@IDCard) v;
GO

結語

將國標演算法封裝為內嵌表值函數，既保證驗證正確性，又能無縫整合進 T-SQL 集合式查詢，滿足批次清洗、約束檢查與業務解析等多樣場景。