TRANSLATE:字符級一對一映射/刪除
TRANSLATE(char, from_str, to_str)
- 逐字符替換:
from_str 中第 i 個字符 → to_str 中第 i 個字符。
from_str **長於** to_str:多出的字符被刪除。
- 任一參數為 NULL → 返回 NULL。
示例
SELECT TRANSLATE('abcdefga', 'abc', 'wo') FROM dual;
-- 結果: wodefgw
-- 解析: a→w, b→o, c→(無對應)刪除, d→d, e→e, f→f, g→g, a→w
實戰:清洗電話號碼(只保留數字)
DELETE FROM PhoneTable
WHERE LENGTH(TRANSLATE(TRIM(Phone), '0123456789' || Phone, '0123456789'))
<> LENGTH(TRIM(Phone));
-- 原理:將所有數字映射到自身,非數字字符在 to_str 中找不到對應被刪除,
-- 若結果長度變短,說明原串含非數字字符。
REPLACE:字符串級整體替換
REPLACE(char, search_str, replacement_str)
- 將
char 中**所有**出現的 search_str 子串替換為 replacement_str。
- 若
search_str 不存在,原樣返回。
- 不支持正則、不支持字符級映射。
示例
SELECT REPLACE('fgsgswsgs', 'fk', 'j') FROM dual; -- 無匹配,返回原串
SELECT REPLACE('fgsgswsgs', 'sg', 'eeerrrttt') FROM dual;
-- 返回: fgeeerrrtttsweeerrrttts (每個 'sg' 替換為 'eeerrrttt')
核心差異總結
| 維度 | TRANSLATE | REPLACE |
| 操作單位 | 單字符 | 子字符串 |
| 映射關係 | 位置一對一 | 整體搜索替換 |
| 刪除字符 | 支持(from>to) | 不直接支持(需替換為空串) |
| 多字符搜索 | 不支持 | 支持 |
| NULL 傳播 | 是 | 否(NULL 視為空串) |
| 典型用途 | 字符集轉換、清洗非法字符、簡單加密/混淆 | 關鍵字替換、模板渲染、格式化 |
選擇建議
- 需**逐字符映射/刪除** →
TRANSLATE(如全半角轉換、去除控制字符)。
- 需**替換固定子串** →
REPLACE(如脫敏、模板佔位符替換)。
- 需**正則替換** → Oracle 10g+
REGEXP_REPLACE。