Oracle 正則表達式函數完整指南：REGEXP_LIKE/INSTR/SUBSTR/REPLACE/COUNT 實戰詳解

Oracle 正則表達式函數完整指南：REGEXP_LIKE/INSTR/SUBSTR/REPLACE/COUNT 實戰詳解

Oracle 10g+ 提供四大正則函數對應傳統字串函數：REGEXP_LIKE (LIKE)、REGEXP_INSTR (INSTR)、REGEXP_SUBSTR (SUBSTR)、REGEXP_REPLACE (REPLACE)，11g 新增 REGEXP_COUNT。本文系統整理呼叫格式、參數詳解、元字符/字符簇/優先級、簡單與複雜實戰案例。

一、統一呼叫格式與參數

REGEXP_INSTR(source_string, pattern
  [, position [, occurrence [, return_option [, match_parameter ]]]])

• source_string：輸入字串
• pattern：正則運算式
• position：起始搜尋位置（預設 1）
• occurrence：第 N 次匹配（預設 1）
• return_option：0=匹配首字元位置，1=匹配後下一字元位置
• match_parameter：i忽略大小寫、c區分大小寫、n點號不匹配換行、m多行模式、x忽略運算式空白

REGEXP_LIKE/REGEXP_SUBSTR/REGEXP_REPLACE/REGEXP_COUNT 參數類似，依功能微調。

二、元字符與字符簇速查

三、運算子優先級（高→低）

1. \ 轉義
2. () (?:) [] 群組
3. * + ? {n} {n,} {n,m} 限定符
4. ^ $ \元字符 位置/順序
5. | 或邏輯

四、簡單案例（測試資料見原文建表腳本）

• REGEXP_LIKE(col, '^a{1,3}')：開頭 1~3 個 a
• REGEXP_LIKE(col, 'a{3}')：連續 3 個 a
• REGEXP_LIKE(col, '^a.*e$')：a 開頭 e 結尾
• REGEXP_LIKE(col, '^[[:alpha:]]+$')：純字母
• REGEXP_LIKE(col, '^[[:lower:][:digit:]]')：小寫或數字開頭
• REGEXP_LIKE(col, '^[^[:digit:]]')：非數字開頭（^ 在 [] 內表否定）

五、複雜實戰案例

1. REGEXP_SUBSTR 擷取郵遞區號

SELECT REGEXP_SUBSTR('The zip code 80831 is for falcon, co', '[[:digit:]]{5}') FROM dual;
-- 80831

2. REGEXP_SUBSTR 第 3 個單字

SELECT REGEXP_SUBSTR('The zip code 80831 is for falcon, co', '[[:alpha:]]{3,}', 1, 3) FROM dual;
-- code

3. REGEXP_SUBSTR 擷取 Email

SELECT REGEXP_SUBSTR('comments or questions - email feedback@plsqlbook.com',
  '[[:alnum:]](([_\.\-\+]?[[:alnum:]]+)*)@([[:alnum:]]+)(([\.-]?[[:alnum:]]+)*)\.([[:alpha:]]{2,})')
FROM dual;
-- feedback@plsqlbook.com

4. REGEXP_INSTR 定位位置

SELECT REGEXP_INSTR('The zip code 80831 is for falcon, co', '[[:digit:]]{5}') FROM dual;
-- 14

5. REGEXP_REPLACE 格式化電話號碼

SELECT REGEXP_REPLACE('Reformat the phone number 719-111-1111 ...',
  '[1]?[-.]?(\(?[[:digit:]]{3}\)?)+[-.]?([[:digit:]]{3})[-.]?([[:digit:]]{4})',
  ' (\1) \2-\3') FROM dual;
-- Reformat the phone number (719) 111-1111 ...

6. REGEXP_LIKE 職稱過濾

SELECT ename, job FROM emp
WHERE REGEXP_LIKE(job, '(clerk|analyst)', 'i');
-- 不分大小寫匹配 clerk 或 analyst

六、效能與最佳實務

• 正則運算較傳統函數消耗 CPU，大量資料集避免在 WHERE 直接使用
• 可建立函數索引加速：CREATE INDEX idx_regex ON t(REGEXP_SUBSTR(col, 'pattern'))
• 優先使用簡單模式，避免過度回溯導致效能劣化
• 12c+ 可用 REGEXP_COUNT 直接計數匹配次數