標準 REPLACE vs 擴展 Replace，精準選型避坑

SQL 標準 REPLACE(str, from_str, to_str) 與 VBA/Access 擴展 Replace(expression, find, replacewith, [start, count, compare]) 同名異義。本文聚焦標準 SQL（MySQL/SQL Server/PostgreSQL/Oracle 通用），補充性能優化與邊界處理。

標準 REPLACE 語法

REPLACE(string_expression1, string_expression2, string_expression3)
-- 將 string_expression1 中所有 string_expression2 替換為 string_expression3

核心特性

• 全局替換：所有匹配出現均替換，非首次
• 區分大小寫：取決於排序規則（如 utf8mb4_general_ci 不區分，utf8mb4_bin 區分）
• NULL 傳播：任一參數為 NULL → 結果 NULL
• 空字串處理：REPLACE('abc', '', 'x') 返回 'abc'（無限循環風險，標準規定返回原串）

實例演示

SELECT REPLACE('abcdefghicde', 'cde', 'xxx');  -- abxxxfghixxx

UPDATE Stu SET name = REPLACE(name, 'abc', '');  -- 批量清洗字段

性能優化策略

1. 避免全表掃描：
   UPDATE t SET col = REPLACE(col, 'old', 'new') WHERE col LIKE '%old%';
LIKE '%old%' 仍可能全掃，大表建議：
2. 分批處理：

-- 每次 5000 行，配合主鍵游標
UPDATE t SET col = REPLACE(col, 'old', 'new')
WHERE id > @last_id AND col LIKE '%old%'
ORDER BY id LIMIT 5000;

3. 索引無效：REPLACE 導致列值改變，無法利用索引定位，只能全表或分區掃描。
4. 觸發器/備份：大批量替換前備份表，或用 pt-online-schema-change 影子表法。

擴展 Replace（VBA/Access/VB6）參考

語法：Replace(expression, find, replacewith, [start, count, compare])

參數	說明
start	搜索起始位置（1-based），默認 1
count	替換次數，默認 -1（全部）
compare	vbBinaryCompare(0)/vbTextCompare(1)/vbDatabaseCompare(2)

⚠️ 不適用於標準 SQL，僅在 VBA/Access/經典 ASP 環境使用。

邊界情況對照表

情況	標準 REPLACE 結果
expression 為空串	空串
expression 為 NULL	NULL（或報錯視實現）
find 為空串	返回 expression 副本
replacewith 為空串	刪除所有 find 出現
start > LEN(expression)	空串
count = 0	返回 expression 副本

總結

標準 REPLACE：三參數、全局替換、NULL傳播、大小寫看排序規則；
大表分批 WHERE LIKE 過濾、備份先行；
VBA Replace 參數豐富但非標準，勿混淆。