SQL Server 2005 身份證驗證與 15 碼轉 18 碼函數完整實作

前言

在企業級應用中，身份證號碼的驗證與格式轉換是常見需求。SQL Server 2005 提供了強大的 T-SQL 程式設計能力，讓我們能將這類業務邏輯封裝為可重用的表值函數。本文將詳細解析一個完整的身份證處理函數 [Helper].[IDCard]，涵輸入驗證、15 碼升級為 18 碼、校驗碼計算、地區碼解析等核心功能。

函數設計目標

• 接受 15 碼（舊版）或 18 碼（新版）身份證號碼
• 自動驗證格式合法性（長度、數字、出生日期、校驗碼）
• 將 15 碼轉換為標準 18 碼格式（補齊出生年份「19」與校驗碼）
• 輸出標準化身份證號、驗證結果、性別、出生日期、地區碼及地區名稱

核心邏輯解析

1. 輸入預處理與基礎驗證

SET @Input = LTRIM(RTRIM(@Card))  -- 去除首尾空白
SET @Length = LEN(@Input)
IF NOT @Length IN (15, 18) GOTO Finish  -- 僅接受 15 或 18 碼

2. 15 碼轉 17 碼本體（補齊世紀）

IF @Length = 15 BEGIN
    IF ISNUMERIC(@Input) = 0 GOTO Finish
    SET @TmpCard = LEFT(@Input, 6) + '19' + RIGHT(@Input, 9)  -- 第 7-8 位補 '19'
    SET @IsOld = 1
END

舊版身份證出生年份僅兩位，預設為 19xx 年；新版前 17 碼直接取用。

3. 出生日期合法性檢查

SET @Birthday = SUBSTRING(@TmpCard, 7, 8)
IF ISDATE(@Birthday) = 0 GOTO Finish  -- 非法日期直接結束

4. 校驗碼計算（GB 11643-1999 標準）

加權因子陣列：[7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2]

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 17 THEN 2 END
    SET @Sum = @Sum + @Num * @WI
    SET @Index = @Index + 1
END
SET @Mod = @Sum % 11
SELECT @Parity = CASE @Mod WHEN 0 THEN '1' WHEN 1 THEN '0' WHEN 2 THEN 'X' ... WHEN 10 THEN '2' END

5. 驗證邏輯與結果組裝

SET @TmpCard = @TmpCard + @Parity  -- 完整 18 碼
IF @IsOld = 1 SET @Valid = 1  -- 15 碼輸入視為有效（無法驗證原校驗碼）
ELSE IF @Parity = RIGHT(@Input, 1) SET @Valid = 1  -- 18 碼需校驗碼匹配
SET @IDCard = @TmpCard  -- 無論驗證通過與否，均輸出計算後的標準 18 碼

進階版擴充：地區碼與性別解析

增強版函數新增輸出欄位：

• Sex：第 17 碼奇偶判斷（1=男，0=女）
• Birthday：YYYYMMDD 格式出生日期
• Region：前 6 碼行政區劃代碼
• RegionName、RegionFullName：透過 [Helper].[RegionCodeFullName] 歷史版本表查詢地區中文名稱

針對首代身份證地區碼可能已廢止，函數會自動回溯至最早版本資料集查詢。

實際應用範例

單筆測試

SELECT * FROM [LzmTWWorks].[Helper].[IDCard] ('110116200808080010')
/* 結果：
Input                  IDCard                 Sex Birthday Region RegionName RegionFullName     Valid
---------------------- ---------------------- ---- -------- ------ ---------- ------------------ -----
110116200808080010     110116200808080014     1   20080808 110116 寬城區      長春市寬城區         0
*/

註：輸入校驗碼為 0，計算應為 4，故 Valid=0，但輸出 IDCard 已修正為正確校驗碼。

批次掃描異常資料

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

利用 CROSS APPLY 逐列呼叫函數，快速篩選出全表中格式錯誤或校驗失效的身份證記錄。

部署與維護建議

1. 將函數部署於專用 Schema（如 Helper），便於權限管理
2. 定期更新 [Helper].[RegionCodeFullName] 歷史地區碼資料表，確保舊版身份證地區解析準確
3. 搭配 CHECK CONSTRAINT 或觸發器，於寫入時即時驗證，提升資料品質

總結

本函數示範了如何在 T-SQL 中實作完整的身份證號碼驗證與轉換邏輯，涵蓋字串操作、日期驗證、模 11 校驗算法、歷史資料相容性處理等關鍵技術。透過表值函數封裝，應用層可像查詢資料表一樣輕鬆呼叫，實現資料庫層面的業務邏輯重用與資料品質把關。