萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

Oracle TRANSLATE vs REPLACE:字符級與字符串級替換的本質區別與實戰場景

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')

核心差異總結

維度TRANSLATEREPLACE
操作單位單字符子字符串
映射關係位置一對一整體搜索替換
刪除字符支持(from>to)不直接支持(需替換為空串)
多字符搜索不支持支持
NULL 傳播是否(NULL 視為空串)
典型用途字符集轉換、清洗非法字符、簡單加密/混淆關鍵字替換、模板渲染、格式化

選擇建議

  • 需**逐字符映射/刪除** → TRANSLATE(如全半角轉換、去除控制字符)。
  • 需**替換固定子串** → REPLACE(如脫敏、模板佔位符替換)。
  • 需**正則替換** → Oracle 10g+ REGEXP_REPLACE。
copyright © 萬盛學電腦網 all rights reserved