SQL Server 身分證驗證函數：15 碼轉 18 碼與合法性檢核

身分證字號驗證是台灣系統常見需求。本文提供一個資料表值函數 [Helper].[IDCard]，支援 15 碼（舊制）自動補齊為 18 碼、出生日期合法性檢查、加權總和校驗碼計算，回傳原始輸入、標準化 18 碼號碼與是否通過驗證。適用於 SQL Server 2005 以上版本。

函數定義

CREATE FUNCTION [Helper].[IDCard] (@Card VARCHAR(18))
RETURNS @TCard TABLE (
    Input VARCHAR(18),
    IDCard VARCHAR(18),
    Valid BIT
)
AS
BEGIN
    DECLARE @Input VARCHAR(18), @IDCard VARCHAR(18), @Valid BIT;
    DECLARE @Length SMALLINT, @TmpCard VARCHAR(18), @IsOld BIT;

    SET @Valid = 0; SET @IDCard = ''; SET @Input = '';

    IF @Card IS NULL GOTO Finish;
    SET @Input = LTRIM(RTRIM(@Card));
    SET @Length = LEN(@Input);
    IF @Length NOT IN (15, 18) GOTO Finish;

    IF @Length = 15 BEGIN
        IF ISNUMERIC(@Input) = 0 GOTO Finish;
        SET @TmpCard = LEFT(@Input, 6) + '19' + RIGHT(@Input, 9);  -- 補齊西元年
        SET @IsOld = 1;
    END ELSE BEGIN
        IF ISNUMERIC(LEFT(@Input, 17)) = 0 GOTO Finish;
        SET @TmpCard = LEFT(@Input, 17);
        SET @IsOld = 0;
    END

    DECLARE @Birthday VARCHAR(8);
    SET @Birthday = SUBSTRING(@TmpCard, 7, 8);
    IF ISDATE(@Birthday) = 0 GOTO Finish;

    -- 加權總和計算
    DECLARE @Sum SMALLINT, @WI TINYINT, @Index TINYINT, @Num TINYINT;
    SET @Sum = 0; SET @Index = 1;
    WHILE @Index < 18 BEGIN
        SET @Num = CAST(SUBSTRING(@TmpCard, @Index, 1) AS TINYINT);
        SELECT @WI = CASE @Index
            WHEN 1 THEN 7 WHEN 2 THEN 9 WHEN 3 THEN 10 WHEN 4 THEN 5
            WHEN 5 THEN 8 WHEN 6 THEN 4 WHEN 7 THEN 2 WHEN 8 THEN 1
            WHEN 9 THEN 6 WHEN 10 THEN 3 WHEN 11 THEN 7 WHEN 12 THEN 9
            WHEN 13 THEN 10 WHEN 14 THEN 5 WHEN 15 THEN 8 WHEN 16 THEN 4
            WHEN 17 THEN 2 END;
        SET @Sum = @Sum + @Num * @WI;
        SET @Index = @Index + 1;
    END

    DECLARE @Mod TINYINT, @Parity VARCHAR(1);
    SET @Mod = @Sum % 11;
    SELECT @Parity = CASE @Mod
        WHEN 0 THEN '1' WHEN 1 THEN '0' WHEN 2 THEN 'X' WHEN 3 THEN '9'
        WHEN 4 THEN '8' WHEN 5 THEN '7' WHEN 6 THEN '6' WHEN 7 THEN '5'
        WHEN 8 THEN '4' WHEN 9 THEN '3' WHEN 10 THEN '2' END;

    SET @TmpCard = @TmpCard + @Parity;  -- 完整 18 碼
    IF @IsOld = 1 SET @Valid = 1;
    ELSE IF @Parity = RIGHT(@Input, 1) SET @Valid = 1;

    SET @IDCard = @TmpCard;
    Finish:
    INSERT INTO @TCard VALUES (@Input, @IDCard, @Valid);
    RETURN;
END;

應用範例：找出員工表中不合法的身分證

SELECT [姓名], [身分證號], b.*
FROM [EmployeeWorks].[Base].[職員]
CROSS APPLY [LzmTWWorks].[Helper].[IDCard](身分證號) b
WHERE [身分證號] IS NOT NULL AND Valid = 0;

說明：函數使用 GOTO 簡化錯誤流程控制，加權因子依中央戶政規定固定。15 碼舊制預設補 '19' 年，若需支援 18/19 世紀出生者，可擴充判斷邏輯。回傳資料表值函數方便搭配 CROSS APPLY 批次驗證。