SQL Server 身分證字號驗證與轉換函數完整實作 (含日期檢核、星期計算)

功能概覽

本文提供三個生產級 T-SQL 函數，涵蓋台灣身分證字號完整驗證邏輯：

1. dbo.IDTrue：驗證 18 碼身分證字號檢查碼正確性
2. dbo.SFZ15To18：15 碼舊版身分證升級為 18 碼新版 (補西元年、重算檢查碼)
3. dbo.ChkYMD / dbo.GetWeekDay：出生日期合法性檢核 (含閏年) 與星期幾計算

核心演算法：檢查碼計算 (ISO 7064 Mod 11,2)

權重陣列 (W)：[7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2] (17 碼)
對應碼表 (A)：索引 0~10 → '1','0','X','9','8','7','6','5','4','3','2'
計算公式：S = Σ(第i碼數值 × W[i]) ; 檢查碼 = A[S % 11]

函數 1：dbo.IDTrue - 18 碼身分證驗證

ALTER FUNCTION dbo.IDTrue (@ID VARCHAR(18))
RETURNS BIT
AS
BEGIN
    DECLARE @RES BIT = 0, @S INT = 0, @i INT = 1;
    DECLARE @W TABLE (rn INT PRIMARY KEY, val INT);
    DECLARE @A TABLE (rwn INT PRIMARY KEY, val CHAR(1));
    
    -- 初始化權重表
    INSERT @W 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);
    -- 初始化對應碼表
    INSERT @A VALUES (0,'1'),(1,'0'),(2,'X'),(3,'9'),(4,'8'),(5,'7'),
                     (6,'6'),(7,'5'),(8,'4'),(9,'3'),(10,'2');

    IF LEN(@ID) = 18
    BEGIN
        WHILE @i <= 17
        BEGIN
            DECLARE @ch CHAR(1) = SUBSTRING(@ID, @i, 1);
            IF @ch LIKE '[0-9]'
            BEGIN
                SET @S += CAST(@ch AS INT) * (SELECT val FROM @W WHERE rn = @i);
                SET @i += 1;
            END
            ELSE RETURN 0; -- 含非數字字元
        END
        
        DECLARE @chk CHAR(1) = (SELECT val FROM @A WHERE rwn = @S % 11);
        IF @chk = SUBSTRING(@ID, 18, 1) SET @RES = 1;
    END
    RETURN @RES;
END

函數 2：dbo.SFZ15To18 - 15 碼升級 18 碼

ALTER FUNCTION dbo.SFZ15To18 (@sfz VARCHAR(15))
RETURNS VARCHAR(18)
AS
BEGIN
    DECLARE @NEWID VARCHAR(18), @S INT = 0, @i INT = 1;
    DECLARE @W TABLE (rn INT PRIMARY KEY, val INT);
    DECLARE @A TABLE (rwn INT PRIMARY KEY, val CHAR(1));
    
    INSERT @W 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),(18,1);
    INSERT @A VALUES (0,'1'),(1,'0'),(2,'X'),(3,'9'),(4,'8'),(5,'7'),
                     (6,'6'),(7,'5'),(8,'4'),(9,'3'),(10,'2');

    -- 插入 '19' 變更為 18 碼 (假設 15 碼年份為民國年)
    SET @NEWID = SUBSTRING(@sfz, 1, 6) + '19' + SUBSTRING(@sfz, 7, 9);
    
    WHILE @i <= 17
    BEGIN
        DECLARE @ch CHAR(1) = SUBSTRING(@NEWID, @i, 1);
        IF @ch LIKE '[0-9]'
            SET @S += CAST(@ch AS INT) * (SELECT val FROM @W WHERE rn = @i);
        ELSE RETURN ''; -- 格式錯誤
        SET @i += 1;
    END
    
    SET @NEWID += (SELECT val FROM @A WHERE rwn = @S % 11);
    RETURN @NEWID;
END

函數 3：dbo.ChkYMD - 日期合法性檢核 (含閏年)

ALTER FUNCTION dbo.ChkYMD (@y INT, @m TINYINT, @d TINYINT, @cy INT)
RETURNS BIT
AS
BEGIN
    DECLARE @res BIT = 1;
    IF @y < 1900 OR @y > @cy RETURN 0;
    
    IF @m IN (1,3,5,7,8,10,12)
        IF @d < 1 OR @d > 31 RETURN 0;
    ELSE IF @m IN (4,6,9,11)
        IF @d < 1 OR @d > 30 RETURN 0;
    ELSE IF @m = 2
    BEGIN
        DECLARE @leap BIT = CASE WHEN (@y % 4 = 0 AND @y % 100 <> 0) OR (@y % 400 = 0) THEN 1 ELSE 0 END;
        IF @d < 1 OR @d > (28 + @leap) RETURN 0;
    END
    ELSE RETURN 0;
    RETURN 1;
END

函數 4：dbo.GetWeekDay - 日期對應星期幾 (Zeller 公式)

ALTER FUNCTION dbo.GetWeekDay (@y INT, @m INT, @d INT)
RETURNS TINYINT
AS
BEGIN
    DECLARE @a TINYINT;
    IF @m IN (1,2) BEGIN SET @m += 12; SET @y -= 1; END
    SET @a = (@d + 2*@m + 3*(@m+1)/5 + @y + @y/4 - @y/100 + @y/400) % 7;
    RETURN @a; -- 0=一, 1=二, ..., 6=日
END

測試範例

-- 驗證 18 碼身分證
SELECT dbo.IDTrue('420324198101031224'); -- 1=合法, 0=不合法

-- 15 碼升級 18 碼
SELECT dbo.SFZ15To18('420324810103153');

-- 日期檢核
SELECT dbo.ChkYMD(1981, 1, 3, YEAR(GETDATE())); -- 1

-- 計算星期
SELECT dbo.GetWeekDay(2004, 12, 10); -- 5 (星期六)

部署建議

• 將函數部署於共用資料庫 (如 UtilityDB)，授予 PUBLIC 執行權限。
• 搭配 CHECK CONSTRAINT 在資料表層強制驗證：ALTER TABLE Member ADD CONSTRAINT CK_ID CHECK (dbo.IDTrue(IDNo)=1)
• 大量資料匯入前先以 dbo.IDTrue 篩選髒資料，再寫入正式表。