SQL Server REPLACE 函數實戰：資料格式批次轉換與清洗

情境引入

專案中 Info 資料表的 Educational 欄位原儲存 JSON 陣列格式（如 [\"Bachelor\",\"Master\"]），因需求變更需改為純 JSON 字串（\"Bachelor,Master\"）。資料量達百萬筆，手工修改不現實，REPLACE 函數 成為最佳解法。

語法與參數詳解

REPLACE ( string_expression, string_pattern, string_replacement )

回傳型別：若輸入為字元型別回傳字元；若為二進位型別回傳二進位。隱含轉換遵循資料型別優先順序。

基本用法範例

-- 將 'abcdefghicde' 中所有 'cde' 替換為 'xxx'
SELECT REPLACE('abcdefghicde', 'cde', 'xxx');
-- 結果：abxxxfghixxx

巢狀 REPLACE 實現多重取代

需同時移除 [ 與 ] 字元：

UPDATE [Info]
SET [Educational] = REPLACE(REPLACE([Educational], '[', ''), ']', '');

• 內層先移除 [，外層再移除 ]。
• 單陳述式完成百萬筆更新，無需 Cursor 或應用程式迴圈。

進階應用場景

1. 統一電話號碼格式

UPDATE Customer
SET Phone = REPLACE(REPLACE(REPLACE(Phone, '-', ''), '(', ''), ')', '');
-- 將 (02)-1234-5676 → 0212345676

2. 移除 HTML 標籤（簡易版）

SELECT REPLACE(REPLACE(Content, '
', ''), '
', '') FROM Articles;

3. 字串模板渲染

DECLARE @tpl VARCHAR(MAX) = '親愛的 {UserName}，您的訂單 {OrderNo} 已出貨。';
SELECT REPLACE(REPLACE(@tpl, '{UserName}', '王小明'), '{OrderNo}', '20240501001');

效能與注意事項

1. 大量更新鎖定風險：百萬筆以上建議分批次（如每批 5 萬筆）配合 TOP 子句更新，避免長時間鎖表。
2. 無法使用索引：WHERE REPLACE(col, 'a', 'b') = 'xxx' 導致掃描，若頻繁查詢請考慮計算欄位或全文檢索。
3. 二進位資料：varbinary(max) 同樣支援，但需確保 pattern/replacement 為同型別。
4. NULL 傳播：任一參數為 NULL，結果即為 NULL；可用 ISNULL/COALESCE 預處理。

替代方案比較

總結

REPLACE 是 T-SQL 字串清洗的基石工具，配合巢狀呼叫可解決 90% 以上的格式轉換需求。記住：**大量寫入請分批、查詢條件勿用於索引欄位、複雜模式改用 CLR 正則**。