Oracle模糊查詢完全指南：LIKE通配符、轉義與性能優化

四大通配符語法與示例

複合條件邏輯

-- 同時包含"三"和"貓"(順序無關)
WHERE name LIKE '%三%' AND name LIKE '%貓%'

-- 以"三"開頭、以"貓"結尾、中間任意
WHERE name LIKE '三%貓%'

-- 精確3字、首字"三"
WHERE name LIKE '三__'

特殊字符轉義：查詢內容含通配符時

-- 問題：查詢字面值 "100%"、 "A_B"、 "[Test]"
-- 解決：使用 ESCAPE 子句自定義轉義字符(推薦 \)
WHERE code LIKE '100\%%' ESCAPE '\'      -- 查 "100%"
WHERE code LIKE 'A\_B' ESCAPE '\'        -- 查 "A_B"
WHERE code LIKE '\[Test\]' ESCAPE '\'    -- 查 "[Test]"

-- 通用轉義函數 (PL/SQL)
CREATE OR REPLACE FUNCTION escape_like(p_str IN VARCHAR2, p_escape IN CHAR DEFAULT '\')
RETURN VARCHAR2 IS
BEGIN
  RETURN REPLACE(REPLACE(REPLACE(p_str, p_escape, p_escape||p_escape), '%', p_escape||'%'), '_', p_escape||'_');
END;
/
-- 使用
WHERE name LIKE escape_like('100%') || '%' ESCAPE '\';

性能優化鐵律

REGEXP_LIKE 正則表達式增強 (10g+)

-- 手機號格式
WHERE phone REGEXP_LIKE '^1[3-9]\d{9}$'

-- 郵箱簡易校驗
WHERE email REGEXP_LIKE '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'

-- 多模式匹配 (等價於多個LIKE OR)
WHERE name REGEXP_LIKE '張|李|王'

實戰最佳實踐

1. 業務允許時優先使用前綴匹配 (col LIKE 'abc%') 利用索引範圍掃描。
2. 不可避免前導%時，評估Oracle Text或功能索引成本收益。
3. 動態拼接SQL時強制使用綁定變量，防注入並利用游標共享：WHERE name LIKE :b1 (應用層構造 '%'+keyword+'%')。
4. 統計信息及時收集(DBMS_STATS.GATHER_TABLE_STATS)，優化器才能正確評估LIKE選擇率。
5. 避免在PL/SQL循環中拼接LIKE字符串，改用SYS_CONTEXT或臨時表傳遞參數。

版本差異提示：
• [范圍]/[^范圍]語法為SQL Server/Access風格，Oracle不原生支援。Oracle僅支援%/_/ESCAPE。
• Oracle 10g+ 引入REGEXP_LIKE/INSTR/SUBSTR/REPLACE正則函數族，功能遠超LIKE。
• 12c+ JSON_EXISTS/JSON_VALUE支援半結構化模糊檢索。
• 本文原內容中的[]/[^]屬誤植，實際Oracle開發請使用REGEXP_LIKE或TRANSLATE實現字符集匹配。