Oracle 正規表達式完整指南：REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、REGEXP_REPLACE 實戰應用

前言

Oracle Database 10g 引入正規表達式功能，提供 REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、REGEXP_REPLACE 四大函數/運算子，大幅強化字串模式比對、擷取、取代能力，超越傳統 LIKE、INSTR、SUBSTR、REPLACE 的限制。

核心元字符速查表

四大函數語法與範例

1. REGEXP_LIKE - 模式比對（用於 WHERE 條件）

-- 找出 ZIP 欄位含非數字字元的列
SELECT zip FROM zipcode
WHERE REGEXP_LIKE(zip, '[^[:digit:]]');

2. REGEXP_INSTR - 回傳模式起始位置

-- 取得郵遞區號（行尾 5 碼數字）起始位置
SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234', '[[:digit:]]{5}$')
FROM dual;  -- 回傳 45

-- 支援選擇性 4 碼擴充碼
SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234', '[[:digit:]]{5}(-[[:digit:]]{4})?$')
FROM dual;  -- 回傳 44

3. REGEXP_SUBSTR - 擷取符合模式的子字串

-- 擷取逗號分隔欄位中的第二欄
SELECT REGEXP_SUBSTR('first field, second field , third field', '[^,]*,')
FROM dual;  -- 回傳 ', second field ,'

4. REGEXP_REPLACE - 模式取代

-- 將多個連續空白取代為單一空白
SELECT REGEXP_REPLACE('Joe   Smith', '( ){2,}', ' ')
FROM dual;  -- 回傳 'Joe Smith'

-- 後向參考：姓名格式轉換 'Ellen Hildi Smith' → 'Smith, Ellen Hildi'
SELECT REGEXP_REPLACE('Ellen Hildi Smith', '(.*) (.*) (.*)', '\3, \1 \2')
FROM dual;  -- 回傳 'Smith, Ellen Hildi'

-- 偵測重複單字
SELECT REGEXP_SUBSTR('The final test is is the implementation', '([[:alnum:]]+)([[:space:]]+)\1')
FROM dual;  -- 回傳 'is is'

比對參數

所有函數支援選用 match_parameter：

• 'i'：不區分大小寫
• 'c'：區分大小寫（預設）
• 'n'：讓 . 比對換行符
• 'm'：多行模式，^/$ 比對每行首尾
• 'x'：忽略模式中的空白註解

實際應用場景

1. 資料驗證：CHECK 條件約束

ALTER TABLE students ADD CONSTRAINT chk_ssn
CHECK (REGEXP_LIKE(ssn, '^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}|[[:digit:]]{9})$'));

接受 123-45-6789 或 123456789 格式。

2. PL/SQL 觸發器自動清洗資料

CREATE OR REPLACE TRIGGER trg_clean_phone
BEFORE INSERT OR UPDATE ON customers
FOR EACH ROW
BEGIN
    :NEW.phone := REGEXP_REPLACE(:NEW.phone, '[^[:digit:]]', '');
END;

3. 匯入檔案欄位切割

SELECT REGEXP_SUBSTR(line, '[^|]+', 1, LEVEL) AS field
FROM import_table
CONNECT BY REGEXP_SUBSTR(line, '[^|]+', 1, LEVEL) IS NOT NULL;

效能建議

• 正規表達式功能強大但 CPU 密集，大量資料集建議先用簡單 LIKE 或 SUBSTR 過濾候選集。
• 避免在索引欄位上使用正規表達式導致全表掃描；可考慮函數式索引（Function-Based Index）。
• 複雜模式建議先在小樣本測試正確性，再套用至正式環境。

結語

正規表達式是處理非結構化文字資料的利器。掌握元字符、量詞、字元類、分組與後向參考，即可解決傳統 SQL 函數難以處理的複雜模式比對、擷取、取代需求。建議將常用模式封裝為 PL/SQL 函數庫，提升團隊開發效率與一致性。