Oracle ESCAPE 轉義字元完整教學：LIKE 查詢中處理萬用字元 % 與 _

問題背景

在 LIKE 模式比對中，%（任意長度字串）與 _（單一字元）為萬用字元。若需查詢實際包含這些字元的資料，必須使用 ESCAPE 子句指定轉義字元。

基本語法

WHERE column LIKE 'pattern' ESCAPE 'escape_character'

• 預設轉義字元常用 \（反斜線）
• 可自訂其他字元，但需避開模式字串本身使用的字元

實戰範例

範例 1：查詢包含底線 _ 的資料

-- 資料：'aa_a', 'aaa'
SELECT * FROM t11 WHERE name LIKE '%_%';
-- 結果：aa_a, aaa （_ 被視為萬用字元，匹配任意單一字元）

SELECT * FROM t11 WHERE name LIKE '%\_%' ESCAPE '\';
-- 結果：aa_a （\_ 代表實際底線字元）

範例 2：斜線 / 作為搜尋字元時的轉義

-- 若搜尋包含 '//' 的字串，需用 '/' 自身作為轉義字元
SELECT * FROM wan_test WHERE psid LIKE '%//%' ESCAPE '/';

若搜尋字元為 /，轉義字元也必須用 /；同理，若搜尋 \，轉義字元用 \。

常見轉義字元對照表

進階應用：動態建構 LIKE 模式

-- PL/SQL 函數：自動轉義使用者輸入的關鍵字
CREATE OR REPLACE FUNCTION escape_like(p_str IN VARCHAR2, p_esc IN VARCHAR2 DEFAULT '\')
RETURN VARCHAR2 IS
BEGIN
  RETURN REPLACE(REPLACE(REPLACE(p_str, p_esc, p_esc||p_esc), '%', p_esc||'%'), '_', p_esc||'_');
END;
/

使用方式：

SELECT * FROM products 
WHERE name LIKE '%' || escape_like(:user_input) || '%' ESCAPE '\';

注意事項

• ESCAPE 字元只能是單一字元
• 模式字串中的轉義字元本身也需轉義（如 \\ 代表實際反斜線）
• ESCAPE 子句為 Oracle 擴充，符合 SQL 標準

掌握 ESCAPE 機制，能精準處理含特殊字元的模糊查詢，避免資料遺漏或誤判。