SQL 驗證身分證字號：長度、出生日期、性別邏輯完整版

身分證字號驗證常用於資料清洗。本文提供針對 15 碼 (舊制) 與 18 碼 (新制) 的純 SQL 驗證邏輯，涵蓋長度、出生日期合法性 (含閏年簡易檢核)、性別碼對照。

1. 長度與出生日期驗證

-- 15 碼: 第 7-12 位為 YYMMDD
-- 18 碼: 第 7-14 位為 YYYYMMDD
WHERE (LEN(SFZ) NOT IN (15, 18)) -- 長度錯誤
   OR (LEN(SFZ) = 15 AND (
        SUBSTRING(SFZ, 9, 2) > '12' -- 月份 > 12
     OR SUBSTRING(SFZ, 11, 2) > '31' -- 日期 > 31
     OR (SUBSTRING(SFZ, 9, 2) IN ('01','03','05','07','08','10','12') AND SUBSTRING(SFZ, 11, 2) > '31')
     OR (SUBSTRING(SFZ, 9, 2) IN ('04','06','09','11') AND SUBSTRING(SFZ, 11, 2) > '30')
     OR (SUBSTRING(SFZ, 9, 2) = '02' AND SUBSTRING(SFZ, 11, 2) > '29') -- 簡易閏年未處理
   ))
   OR (LEN(SFZ) = 18 AND (
        SUBSTRING(SFZ, 7, 2) NOT IN ('19','20') -- 年份前綴非 19/20
     OR SUBSTRING(SFZ, 11, 2) > '12'
     OR SUBSTRING(SFZ, 13, 2) > '31'
     OR (SUBSTRING(SFZ, 11, 2) IN ('01','03','05','07','08','10','12') AND SUBSTRING(SFZ, 13, 2) > '31')
     OR (SUBSTRING(SFZ, 11, 2) IN ('04','06','09','11') AND SUBSTRING(SFZ, 13, 2) > '30')
     OR (SUBSTRING(SFZ, 11, 2) = '02' AND SUBSTRING(SFZ, 13, 2) > '29')
   ))

2. 性別碼驗證

-- 15 碼: 第 15 位 奇數=男, 偶數=女
-- 18 碼: 第 17 位 奇數=男, 偶數=女
WHERE (LEN(SFZ) = 15 AND (
        (SUBSTRING(SFZ, 15, 1) IN ('1','3','5','7','9') AND 性別 <> '男')
     OR (SUBSTRING(SFZ, 15, 1) IN ('0','2','4','6','8') AND 性別 <> '女')
   ))
   OR (LEN(SFZ) = 18 AND (
        (SUBSTRING(SFZ, 17, 1) IN ('1','3','5','7','9') AND 性別 <> '男')
     OR (SUBSTRING(SFZ, 17, 1) IN ('0','2','4','6','8') AND 性別 <> '女')
   ))

3. 進階：校驗碼演算法 (18 碼)

18 碼最後一位為 ISO 7064:1983 MOD 11-2 校驗碼，可進一步寫函數驗證：

CREATE FUNCTION dbo.ValidateID18(@id char(18)) RETURNS BIT
AS BEGIN
  DECLARE @w TABLE (i int, w int);
  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);
  DECLARE @sum int = 0, @i int = 1;
  WHILE @i <= 17 BEGIN
    SET @sum += CAST(SUBSTRING(@id, @i, 1) AS int) * (SELECT w FROM @w WHERE i = @i);
    SET @i += 1;
  END
  DECLARE @mod int = @sum % 11;
  DECLARE @check char(1) = 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;
  RETURN CASE WHEN @check = SUBSTRING(@id, 18, 1) THEN 1 ELSE 0 END;
END

Access 相容性

Access 不支援 SUBSTRING，請改用 MID(欄位, 起始, 長度)。