Oracle 正則表達式四大函數完整指南 (REGEXP_LIKE/SUBSTR/INSTR/REPLACE)

四大核心函數總覽

通用參數說明

函數( source_char, pattern [, position [, occurrence [, match_parameter ] ] ] )
REGEXP_REPLACE 另加 replace_string 參數

• source_char：來源字串（欄位/常數/變數）
• pattern：正則表達式模式
• position：起始搜尋位置（預設 1）
• occurrence：第 N 次匹配（預設 1）
• match_parameter：匹配模式修飾符
  • i：不分大小寫
  • c：分大小寫
  • n：`.` 不匹配換行
  • m：多行模式 (`^$` 匹配行首尾)
  • x：忽略模式中的空白
• replace_string：替換字串（可用 `\1` `\2` 反向引用捕獲組）

常用字符類別 (POSIX)

運算子優先序 (高→低)

1. `\` 轉義
2. `()` `(?:)` `(?=)` `[]` 分組與字符類
3. `*` `+` `?` `{n}` `{n,}` `{n,m}` 量詞
4. `^` `$` 任意元字符 位置錨定
5. `|` 邏輯 OR

測試資料建立

CREATE TABLE test (mc VARCHAR2(60));
INSERT INTO test VALUES ('112233445566778899');
INSERT INTO test VALUES ('22113344 5566778899');
INSERT INTO test VALUES ('33112244 5566778899');
INSERT INTO test VALUES ('44112233 5566 778899');
INSERT INTO test VALUES ('5511 2233 4466778899');
INSERT INTO test VALUES ('661122334455778899');
INSERT INTO test VALUES ('771122334455668899');
INSERT INTO test VALUES ('881122334455667799');
INSERT INTO test VALUES ('991122334455667788');
INSERT INTO test VALUES ('aabbccddee');
INSERT INTO test VALUES ('bbaaaccddee');
INSERT INTO test VALUES ('ccabbddee');
INSERT INTO test VALUES ('ddaabbccee');
INSERT INTO test VALUES ('eeaabbccdd');
INSERT INTO test VALUES ('ab123');
INSERT INTO test VALUES ('123xy');
INSERT INTO test VALUES ('007ab');
INSERT INTO test VALUES ('abcxy');
INSERT INTO test VALUES ('The final test is is is how to find duplicate words.');
COMMIT;

1. REGEXP_LIKE 範例

-- 以 1~3 個 a 開頭
SELECT * FROM test WHERE REGEXP_LIKE(mc, '^a{1,3}');

-- 任意位置含 1~3 個 a
SELECT * FROM test WHERE REGEXP_LIKE(mc, 'a{1,3}');

-- 以 a 開頭、e 結尾
SELECT * FROM test WHERE REGEXP_LIKE(mc, '^a.*e$');

-- 開頭為小寫字母或數字
SELECT * FROM test WHERE REGEXP_LIKE(mc, '^[[:lower:]]|[[:digit:]]');

-- 開頭為小寫字母
SELECT * FROM test WHERE REGEXP_LIKE(mc, '^[[:lower:]]');

-- 含非數字字符
SELECT mc FROM test WHERE REGEXP_LIKE(mc, '[^[:digit:]]');

-- 開頭為非數字
SELECT mc FROM test WHERE REGEXP_LIKE(mc, '^[^[:digit:]]');

2. REGEXP_INSTR 範例

-- 字串結尾第一個數字位置
SELECT REGEXP_INSTR(mc, '[[:digit:]]$') FROM test;

-- 字串結尾連續數字起始位置
SELECT REGEXP_INSTR(mc, '[[:digit:]]+$') FROM test;

-- 找 $ 後緊接數字
SELECT REGEXP_INSTR('The price is $400.', '\$[[:digit:]]+') FROM dual;

-- 找第一個非小寫字母位置
SELECT REGEXP_INSTR('onetwothree', '[^[:lower:]]') FROM dual;

-- 找第一個非逗號字符 (貪婪 *)
SELECT REGEXP_INSTR(',,,,,', '[^,]*') FROM dual;  -- 回傳 1 (空字串匹配)

-- 找第一個非逗號字符 (非貪婪)
SELECT REGEXP_INSTR(',,,,,', '[^,]') FROM dual;   -- 回傳 6 (無匹配)

3. REGEXP_SUBSTR 範例

-- 擷取第一組連續小寫字母
SELECT REGEXP_SUBSTR(mc, '[a-z]+') FROM test;

-- 擷取第一組連續數字
SELECT REGEXP_SUBSTR(mc, '[0-9]+') FROM test;

-- 從開頭匹配到第一個 b
SELECT REGEXP_SUBSTR('aababcde', '^a.*b') FROM dual;  -- 回傳 'aab'

4. REGEXP_REPLACE 範例

-- 將 2 個以上連續空格替換為單一逗號
SELECT REGEXP_REPLACE('Joe Smith', '( ){2,}', ',') AS rx_replace FROM dual;
-- 結果: 'Joe,Smith'

-- 反向引用重組三個單詞
SELECT REGEXP_REPLACE('aa bb cc', '(.*) (.*) (.*)', '\3, \2, \1') FROM dual;
-- 結果: 'cc, bb, aa'

效能提醒

• 正則表達式比 `LIKE`/`SUBSTR` 耗資源，大量資料建議配合函數索引：

  CREATE INDEX idx_test_regexp ON test (REGEXP_SUBSTR(mc, '[0-9]+'));

• 避免在 `WHERE` 子句對大表欄位直接使用正則，優先用簡單條件過濾後再套用。