SQL Server REPLACE 函數巧用：精確查詢包含特定子字串的資料

REPLACE 函數常用於字串替換，但透過「反向思維」也能精確判斷字串是否包含特定子字串，且不受 LIKE 模糊匹配的干擾。

問題場景

資料表 Table_5 欄位 strNum 儲存逗點分隔的代碼串：

需求：查出 精確包含 ",aaa," 的列 (ID 1、3)，排除 包含 ",aaaa," 的列 (ID 2)。

為什麼 LIKE 不行？

-- 會錯誤匹配 ID 2 (含 aaaa)
SELECT * FROM Table_5 WHERE strNum LIKE '%,aaa,%';

REPLACE 精確匹配技巧

核心邏輯：將目標子字串替換為空字串，若結果與原字串不同，則證明原字串包含該子字串。

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

步驟解析

1. ',' + strNum + ','：首尾補齊逗點，確保邊界代碼也能被精確匹配
2. REPLACE(..., ',aaa,', '')：若存在精確的 ",aaa," 則被移除
3. <> 原字串：替換前後不同 → 存在目標子字串

效能與擴充考量

• 索引利用：此寫法無法使用索引 (SARGable 問題)，適合中小資料量或非高頻查詢
• 大資料量優化：
  • 新增計算欄位 HasAaa AS CASE WHEN strNum LIKE '%,aaa,%' THEN 1 ELSE 0 END 並建索引
  • 或使用全文檢索 CONTAINS(strNum, '","aaa","')
  • 正規化設計：將代碼拆分至子表 Table_Codes(ParentID, Code)，用 JOIN + WHERE Code='aaa' 查詢
• 通用化函數：可封裝為標量函數 dbo.ContainsExactCode(strNum, 'aaa') 供重複使用

對比測試

-- 測試資料
DECLARE @t TABLE (id INT, strNum VARCHAR(100));
INSERT @t VALUES 
(1, ',aaa,bbb,'), (2, ',aaaa,bbb,'), (3, ',bbb,aaa,'), (4, ',ccc,ddd,');

-- REPLACE 精確匹配
SELECT * FROM @t 
WHERE REPLACE(','+strNum+',', ',aaa,', '') <> ','+strNum+',';
-- 結果：ID 1, 3 ✓

-- LIKE 模糊匹配 (錯誤包含 ID 2)
SELECT * FROM @t WHERE strNum LIKE '%,aaa,%';
-- 結果：ID 1, 2, 3 ✗