四大核心函數語法速查
| 函數 | 語法 | 返回 | 核心用途 |
|---|
| REGEXP_LIKE | REGEXP_LIKE(src, pattern [, match_param]) | BOOLEAN | WHERE 子句模式匹配 |
| REGEXP_INSTR | REGEXP_INSTR(src, pattern [, pos [, occ [, ret_opt [, match_param]]]]) | NUMBER | 匹配位置（支援第 N 次、返回結尾） |
| REGEXP_SUBSTR | REGEXP_SUBSTR(src, pattern [, pos [, occ [, match_param]]]) | VARCHAR2 | 提取匹配子串 |
| REGEXP_REPLACE | REGEXP_REPLACE(src, pattern [, repl [, pos [, occ [, match_param]]]]) | VARCHAR2 | 模式替換（支援後向引用重組） |
match_param：'i' 不區分大小寫、'c' 區分、'n' 讓 . 匹配換行、'm' 多行模式 ^$ 匹配行首尾。
元字符完整分類表
| 類別 | 元字符 | 說明 |
|---|
| 定位 | ^ | 行首（多行模式下為每行首） |
| $ | 行尾（多行模式下為每行尾） |
| 量詞 | * | 0 次或多次（貪婪） |
| + | 1 次或多次 |
| ? | 0 次或 1 次 |
| {m} | 恰好 m 次 |
| {m,} | 至少 m 次 |
| {m,n} | m 到 n 次 |
| 分組/選擇 | () | 捕獲組，供後向引用 \1..\9 |
| | | 或選擇 |
| 字符類 | [abc] | 單字符集合 |
| [^abc] | 排除集合 |
| [a-z] | 範圍 |
POSIX 字符類（必須置於 [] 內）
| 類別 | 匹配內容 |
|---|
| [:alpha:] | 字母 |
| [:digit:] | 數字 0-9 |
| [:alnum:] | 字母數字 |
| [:lower:] / [:upper:] | 小寫/大寫字母 |
| [:space:] | 空白（空格、製表、換行等） |
| [:punct:] | 標點符號 |
| [:print:] | 可打印字符 |
| [:cntrl:] | 控制字符 |
用法：REGEXP_LIKE(col, '[[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}') 驗證 SSN 格式。
實戰範例庫
1. 數據質量檢查：非數字字符檢測
SELECT zip FROM zipcode
WHERE REGEXP_LIKE(zip, '[^[:digit:]]'); -- 含非數字
2. 郵政編碼提取：行尾 5 位或 5+4 位
-- 僅 5 位
SELECT REGEXP_INSTR(addr, '[[:digit:]]{5}$') FROM dual; -- 返回起始位置
-- 5 位 + 可選 4 位
SELECT REGEXP_INSTR(addr, '[[:digit:]]{5}(-[[:digit:]]{4})?$') FROM dual;
3. CSV 欄位提取：逗號分隔值
SELECT REGEXP_SUBSTR('a,b,c', ',[^,]*,') FROM dual; -- 返回 ",b,"
-- 12c+ 更優：REGEXP_SUBSTR(str, '[^,]+', 1, n) 取第 n 欄
4. 多空格壓縮為單空格
SELECT REGEXP_REPLACE('a b c', '( ){2,}', ' ') FROM dual; -- 'a b c'
5. 後向引用：姓名格式重組 "First Last" → "Last, First"
SELECT REGEXP_REPLACE('John Doe', '(.*) (.*)', '\2, \1') FROM dual; -- 'Doe, John'
6. 重複單詞檢測
SELECT REGEXP_SUBSTR('the the test', '([[:alnum:]]+)[[:space:]]+\1') FROM dual; -- 'the the'
7. 列級約束：社會安全號碼驗證
ALTER TABLE students ADD CONSTRAINT chk_ssn
CHECK (REGEXP_LIKE(ssn, '^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}|[[:digit:]]{9})$'));
效能與最佳實踐
- 錨定 ^$：未錨定模式導致全掃描，必加
^pattern$ 利用索引（函數索引）。
- 避免過度貪婪：
.* 易導致回溯爆炸，改用 .*?（10g 不支援非貪婪，需具體化模式）。
- 函數索引加速：
CREATE INDEX idx_zip_clean ON zipcode(REGEXP_REPLACE(zip, '\D', ''));
SELECT * FROM zipcode WHERE REGEXP_REPLACE(zip, '\D', '') = '12345';
- PL/SQL 封裝：將常用正則封裝為函數，統一維護、便於單元測試。
對比：REGEXP vs 傳統 LIKE/INSTR/SUBSTR/REPLACE
| 能力 | LIKE/傳統 | REGEXP |
|---|
| 重複次數控制 | 無 | {m,n} 精確控制 |
| 字符範圍/類別 | 無 | POSIX 類、自定義 [] |
| 選擇分支 | 無 | | 多模式或 |
| 捕獲重組 | 無 | 後向引用 \1..\9 |
| 多行模式 | 無 | 'm' 參數 |
| 效能 | 極快（簡單模式） | 較慢（複雜模式），需函數索引優化 |
正則表達式是文本處理的「瑞士軍刀」，掌握四大函數、元字符、POSIX 類、後向引用，配合函數索引，可解決 99% 複雜文本驗證、提取、清洗、重組需求。
