Oracle VARCHAR2 轉 NUMBER 數據清洗：正則表達式一行搞定「聯系電話」臟數據

業務背景：「聯系電話」字段設計為 VARCHAR2，導致混入中文、英文、特殊符號

理想設計：字段定義為 NUMBER + 前端輸入校驗。現實：歷史系統無約束，數據已入庫，需清洗遷移。

臟數據樣本

PHONE
---------------
13800138000
021-88888888
(010) 66668888
張三 13912345678
聯系電話：13600001111
無電話
N/A
NULL

傳統思路：PL/SQL 函數 + CASE WHEN 分支判斷（複雜、低效）

編寫函數 clean_phone(p_str VARCHAR2) RETURN NUMBER，內部多層 IF/ELSE、REGEXP_LIKE、SUBSTR、TRANSLATE 組合，維護成本極高。

DBA 絕招：單條 SQL 正則提取純數字

SELECT 
    phone AS original,
    REGEXP_REPLACE(phone, '[^0-9]', '') AS digits_only,
    CASE 
        WHEN REGEXP_LIKE(REGEXP_REPLACE(phone, '[^0-9]', ''), '^1[3-9]\d{9}$') THEN '手機'
        WHEN REGEXP_LIKE(REGEXP_REPLACE(phone, '[^0-9]', ''), '^0\d{2,3}\d{7,8}$') THEN '座機'
        ELSE '異常'
    END AS type
FROM contact_table;

核心函數解析

• REGEXP_REPLACE(phone, '[^0-9]', '')：刪除所有非數字字符，保留純數字串
  • [^0-9]：匹配任意非 0-9 字符
  • 替換為空串 ''，等價於「只保留數字」
• REGEXP_LIKE(str, pattern)：判斷清洗後字符串是否符合手機/座機格式

清洗結果示例

落地 ETL 更新語句

UPDATE contact_table
SET phone_clean = REGEXP_REPLACE(phone, '[^0-9]', ''),
    phone_type = CASE
        WHEN REGEXP_LIKE(REGEXP_REPLACE(phone, '[^0-9]', ''), '^1[3-9]\d{9}$') THEN 'MOBILE'
        WHEN REGEXP_LIKE(REGEXP_REPLACE(phone, '[^0-9]', ''), '^0\d{2,3}\d{7,8}$') THEN 'LANDLINE'
        ELSE 'INVALID'
    END,
    clean_time = SYSDATE
WHERE phone_clean IS NULL;  -- 增量清洗

避坑指南

1. REGEXP_REPLACE 需 Oracle 10g+，10gR1 僅支持 REGEXP_LIKE/INSTR/SUBSTR，舊版本用 TRANSLATE + 多次 REPLACE
2. 手機號正則隨運營商擴號更新，建議維護在配置表中動態加載
3. 清洗前**必須備份原表**，驗證抽樣無誤再全量執行
4. 大表分批次提交（WHERE rownum <= 10000 循環），避免 Undo 表空間爆滿

總結

「設計之初定 NUMBER」是根治，「正則一行提取數字」是救命稻草。REGEXP_REPLACE(col, '[^0-9]', '') 配合 REGEXP_LIKE 分類，將 VARCHAR2 臟數據清洗為標準 NUMBER，簡潔高效、易維護、可增量。新系統**堅決拒絕**電話、身份證、銀行卡等純數字業務鍵使用 VARCHAR2 存儲。