資料清洗與遷移時，常需對欄位內容做局部替換、去除雜訊或格式標準化。本文蒐集多種 UPDATE ... REPLACE、TRIM、CONCAT 組合技巧，涵蓋關鍵字替換、空白清理、特殊字元移除、日期格式統一等高頻場景，可直接套用至生產環境。

核心語法模板

UPDATE `table_name` 
SET `col` = REPLACE(`col`, '舊字串', '新字串')
WHERE INSTR(`col`, '舊字串') > 0;

實戰範例速查

1. 關鍵字替換（如系統訊息本地化）

-- 將私訊主旨中的 "Welcome to" 改為 "歡迎光臨"
UPDATE `cdb_pms` 
SET `subject` = REPLACE(`subject`, 'Welcome to', '歡迎光臨')
WHERE INSTR(`subject`, 'Welcome to') > 0;

2. 連結/參數批量修正

-- 修正貼文中的主題連結 tid
UPDATE `cdb_posts` 
SET `message` = REPLACE(`message`, 'viewthread.php?tid=3989', 'viewthread.php?tid=16546')
WHERE INSTR(`message`, 'viewthread.php?tid=3989') > 0;

3. 去除前後空白

UPDATE `es_product` SET `pro_pub_time` = TRIM(`pro_pub_time`);

4. 移除特定符號（[ ] . 等）

UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '[', '') WHERE INSTR(`pro_pub_time`, '[') > 0;
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, ']', '') WHERE INSTR(`pro_pub_time`, ']') > 0;
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '.', '-') WHERE INSTR(`pro_pub_time`, '.') > 0;

5. 全形/半形統一（中文破折號 → 英文連字號）

UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '－', '-') WHERE INSTR(`pro_pub_time`, '－') > 0;

6. 中文日期格式轉標準格式

UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '年', '-') WHERE INSTR(`pro_pub_time`, '年') > 0;
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '月', '-') WHERE INSTR(`pro_pub_time`, '月') > 0;

7. 補齊不完整日期（如 2005-04- → 2005-04-01）

UPDATE `es_product` 
SET `pro_pub_time` = CONCAT(`pro_pub_time`, '01')
WHERE SUBSTRING_INDEX(`pro_pub_time`, '-', -1) = '' 
  AND LENGTH(`pro_pub_time`) > 0 AND LENGTH(`pro_pub_time`) > 5;

8. 統一轉為 YYYY-MM-DD 後再格式化顯示

UPDATE `es_product` 
SET `pro_pub_time` = DATE_FORMAT(`pro_pub_time`, '%Y年%m月')
WHERE INSTR(`pro_pub_time`, '-') > 0;

執行前必做三件事

1. 備份資料：大量 UPDATE 無法回滾（除非在交易中）。
2. 先跑 SELECT 驗證：把 SET 換成 SELECT 確認影響筆數與結果。
3. 加上 WHERE 過濾：避免全表掃描與誤改無關列。

善用這些模板，能大幅降低人工清洗成本，確保資料一致性與可用性。