Oracle 10g 內建 SQL 正則表達式：REGEXP 函數家族完整指南

背景

Oracle 8/9i 僅支援簡單的 LIKE 模式匹配，Oracle 10g 終內建符合 POSIX 標準的正則表達式，提供四大核心函數，語法與傳統字串函數對應：

POSIX 元字元速查

• ^ 字串開頭 / $ 字串結尾
• . 任意單一字元
• [a-z] 字元範圍 ≡ [[:lower:]]
• ? 0 或 1 次 / + 1 次以上 / * 0 次以上
• {m,n} m~n 次 / {m} 剛好 m 次 / {m,} 至少 m 次
• (...) 分組 / | 替代 (OR)

範例：'^([a-z]+|[0-9]+)$' 僅匹配全小寫字母或全數字字串。

四大函數實戰範例

1. REGEXP_LIKE - 進階過濾與約束

-- 大小寫不敏感匹配以 JA 或 JO 開頭的員工
WHERE REGEXP_LIKE(ename, '^J[AO]', 'i')

-- CHECK 約束：ename 僅允許字母數字 (無空格標點)
ALTER TABLE emp ADD CONSTRAINT regex01
CHECK (REGEXP_LIKE(ename, '^[[:alnum:]]+$'));

違反約束拋出 ORA-02290。

2. REGEXP_INSTR - 定位匹配位置

-- 找出第一個 $金額 位置
SELECT REGEXP_INSTR('The total is $400 for your purchase.', '\$[[:digit:]]+')
FROM dual;  -- 回傳 14

-- 進階參數：出現次數、搜尋起始位、回傳匹配後位置

3. REGEXP_SUBSTR - 擷取匹配子串

-- 逗號分隔值取第一欄
SELECT REGEXP_SUBSTR('one,two,three', '[^,]*') FROM dual;  -- 'one'

4. REGEXP_REPLACE - 替換與轉換

-- 華氏轉攝氏
SELECT REGEXP_REPLACE('The temperature is 23°F',
                      '([[:digit:]]+)°F',
                      '(\1-32)*5/9||''°C''')
FROM dual;  -- 'The temperature is -5°C'

注意替換字串中 \1 代表第一個捕獲群組，需在 SQL 字串中雙重轉義。

應用場景建議

• 資料清洗：電話、郵件、身分證格式驗證與標準化。
• ETL 處理：非結構化日誌解析、欄位拆分。
• 資料品質約束：CHECK 條件與函數索引加速查詢。
• 字串轉換：單位換算、編碼轉換、敏感資訊遮罩。

效能提示

正則表達式功能強大但較傳統函數耗費 CPU。大量資料處理時，建議：

1. 優先使用簡單 LIKE/INSTR 完成可行案例。
2. 建立函數索引：CREATE INDEX idx_emp_name_regex ON emp (REGEXP_SUBSTR(ename, '^[A-Z]+'));
3. 避免在高頻 OLTP 查詢的 WHERE 條件中使用複雜正則。