Oracle 模糊查詢 LIKE 與 REGEXP_LIKE 完整指南

Oracle 模糊查詢 LIKE 與 REGEXP_LIKE 完整指南

模糊查詢是業務開發高頻需求。Oracle 提供 LIKE（通配符）與 REGEXP_LIKE（正則表達式）兩套機制，本文系統整理語法、通配符、特殊字符轉義與進階模式匹配技巧。

LIKE 通配符完整對照表

四種核心匹配模式

1. % - 任意多字元（最常用）

-- 包含「三」的所有記錄
SELECT * FROM user_table WHERE u_name LIKE '%三%';

-- 同時包含「三」與「貓」（需 AND 連接）
SELECT * FROM user_table WHERE u_name LIKE '%三%' AND u_name LIKE '%貓%';

-- 順序敏感：先「三」後「貓」
SELECT * FROM user_table WHERE u_name LIKE '%三%貓%';

2. _ - 單一字元定位

-- 三字名、中間字為「三」
SELECT * FROM user_table WHERE u_name LIKE '_三_';

-- 三字名、首字為「三」
SELECT * FROM user_table WHERE u_name LIKE '三__';

3. [] - 字元集合/範圍（10g+）

-- 指定姓氏
SELECT * FROM user_table WHERE u_name LIKE '[張李王]三';

-- 數字範圍
SELECT * FROM user_table WHERE u_name LIKE '老[1-9]';  -- 老1~老9

-- 字母範圍
SELECT * FROM user_table WHERE u_name LIKE 'Code_[A-C]';

4. [^] - 排除集合（10g+）

-- 排除特定姓氏
SELECT * FROM user_table WHERE u_name LIKE '[^張李王]三';

-- 排除數字範圍
SELECT * FROM user_table WHERE u_name LIKE '老[^1-4]';  -- 老5、老6...

REGEXP_LIKE 正則表達式（10g+ 強力補強）

LIKE 不支援完整正則，複雜模式需用 REGEXP_LIKE。

常用正則模式

實戰範例

-- 手機號碼格式驗證
SELECT * FROM customer WHERE REGEXP_LIKE(mobile, '^1[3-9]\d{9}$');

-- Email 簡易驗證
SELECT * FROM customer WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- 中文姓名（2-4 個中文字）
SELECT * FROM employee WHERE REGEXP_LIKE(ename, '^[\u4e00-\u9fa5]{2,4}$');

特殊字符轉義函數

動態構建 LIKE 條件時，需轉義使用者輸入中的通配符。

CREATE OR REPLACE FUNCTION sql_encode(p_str IN VARCHAR2) RETURN VARCHAR2 IS
   v_result VARCHAR2(4000) := p_str;
BEGIN
   v_result := REPLACE(v_result, '\', '\\');   -- 必須最先處理
   v_result := REPLACE(v_result, '%', '\%');
   v_result := REPLACE(v_result, '_', '\_');
   v_result := REPLACE(v_result, '[', '\[');
   v_result := REPLACE(v_result, ']', '\]');
   v_result := REPLACE(v_result, '^', '\^');   -- 若作為排除字元首位
   RETURN v_result;
END;
/

使用方式：WHERE name LIKE '%' || sql_encode(:input) || '%' ESCAPE '\'

效能考量

1. 前導 % 無法使用索引：LIKE '%abc%' 全表掃描；LIKE 'abc%' 可用索引範圍掃描
2. REGEXP_LIKE 幾乎不使用索引，大表需謹慎使用
3. 考慮 Oracle Text（Context 索引）實現全文檢索需求
4. 函數索引：CREATE INDEX idx_upper_name ON emp(UPPER(ename)) 支援大小寫不敏感查詢

多語言環境注意事項

• NLS_SORT 影響字元比較順序（如 BINARY_CI 不分大小寫）
• 中文標點/全形字元需統一正規化後再比對
• 正則中的 \w、\d 僅匹配 ASCII，中文需用 Unicode 範圍