SQL Server 利用 REPLACE 反向判斷字串是否包含特定子字串技巧

情境

資料表 Table_5 欄位 strNum 存放逗號分隔代碼 (如 ,aaa,bbb,)，需查詢 **精確包含 `aaa`** 的列，**排除 `aaaa`** 等較長代碼。

傳統 LIKE 困境

WHERE strNum LIKE '%,aaa,%'  -- 仍會匹配 ',aaaa,' (因為 aaaa 含 aaa)

REPLACE 反向邏輯

若字串包含目標子字串，REPLACE 會將其移除，導致結果字串 **長度改變 / 內容不同**。利用此特性：

SELECT *
FROM [master].[dbo].[Table_5]
WHERE REPLACE(',' + strNum + ',', ',aaa,', '') <> ',' + strNum + ',';

原理解析

1. 在首尾加入分隔符 `,` 確保邊界完整 (避免 `aaa` 在開頭/結尾被遺漏)。
2. REPLACE(full_str, ',aaa,', '') 若找到目標 → 移除 → 字串變短 → `<>` 原字串 → **符合條件**。
3. 若未找到 → 字串不變 → `=` 原字串 → **不符合條件**。

效能考量

• REPLACE 為純量函數，**無法使用索引**，全表掃描。
• 資料量大時建議：
  ① 正規化設計：建立對應表 Table_Code (id, code)，用 JOIN / EXISTS。
  ② 計算欄位 + 索引：ALTER TABLE ADD has_aaa AS CASE WHEN ... THEN 1 ELSE 0 END PERSISTED。
  ③ 全文檢索 / JSON 陣列 (SQL 2016+)。

擴充：多值精確匹配

-- 同時包含 aaa 與 bbb
WHERE REPLACE(REPLACE(','+strNum+',', ',aaa,', ''), ',bbb,', '') <> ','+strNum+',';

結語

REPLACE 反向判斷是**不改變結構、快速原型**的實用技巧，但**不適合大量資料高頻查詢**。長期專案請採用正規化關聯設計或計算欄位索引。