SQL LIKE 多條件模糊匹配實戰：CHARINDEX/INSTR 替代方案

問題場景

表 A 欄位 name 存逗號分隔字串（'lu,li,zhang'），表 B 有名單（li, lu, zhou, zhang）。需查 A 中包含 B 名單中任一名字的行，且 B 篩選 sex=1。

❌ 錯誤嘗試

SELECT * FROM A 
WHERE A.name LIKE (SELECT B.name FROM B WHERE B.sex = 1);
-- 子查詢返回多行，LIKE 不支援集合比較

✅ SQL Server 解法：CHARINDEX + 連接

SELECT DISTINCT a.no, a.name
FROM A a
JOIN B b ON CHARINDEX(b.name, a.name) > 0
WHERE b.sex = 1;

• CHARINDEX(sub, str) 返回子串首次出現位置（1-based），0 表不含
• DISTINCT 去重：一行 A 可能匹配多個 B 名字
• 效能：無索引支援，全表掃描。大資料量建議正規化拆表或全文檢索

✅ Oracle 解法：INSTR + 連接

SELECT DISTINCT a.no, a.name
FROM A a, B b
WHERE INSTR(a.name, b.name) > 0
  AND b.sex = 1;

• INSTR(str, sub) 參數順序與 CHARINDEX 相反，返回位置（1-based）
• 範例：INSTR('Hello World', 'or') = 8

⚠️ 關鍵提醒

1. 資料模型反模式：逗號分隔值違反 1NF，查詢/維護皆痛苦。正解：建立關聯表 A_Name (a_id, name)
2. 效能陷阱：CHARINDEX/INSTR 導致掃描，無法使用索引。百萬級資料需全文索引或拆表
3. 邊界匹配：'li' 會誤匹配 'liu'。改用 ',' + name + ',' LIKE '%,' + b.name + ',%'（仍無索引）

最佳實踐建議