[字串函數]字串取代

以新字串取代目標字串中的特定（舊）字串，依功能區分成：

(1).符合字串-置換(REPLACE)

(2).字元對應-置換(TRANSLATE)

(2).位置置換/填入/移除(STUFF)：MSSQL提供

(4).正規表示式(REGEXP_REPLACE)：ORACLE提供

指令說明:

ORACLE: 提供REPLACE、TRANSLATE及REGEXP_REPLACE函數，由於REGEXP_REPLACE相對複雜，將撰專文說明。

語法	說明
REPLACE(str, old_str [,new_str])	字串(str)中所有特定(舊)字串(old_str) 以新字串(new_str)取代。new_str預設為空字串。
TRANSLATE(str, from, to)	將字串(str)依from與to間以字元一對一對應取代。

MSSQL: 提供REPLACE(子字串)、STUFF(位置)及TRANSLATE(2017)三個函數。

語法	說明
REPLACE(str, old_str ,new_str)	字串(str)中所有特定(舊)字串(old_str) 以新字串(new_str)取代。
STUFF(str, i, n, new_str)	字串str中從位置i起移除n個字元，再以new_str填入 STUFF(原字串, 起始位置, 移除長度, 替換字串)
TRANSLATE(str, from, to)	將字串(str)依from與to間以字元一對一對應取代。 (2017版)

REPLACE指令比較:

在字串(str)中搜尋中所有特定(舊)字串(old_str) 以新字串(new_str)取代。

	ORACLE	MSSQL
語法	REPLACE(str, old_str [,new_str])	REPLACE(str, old_str ,new_str)
置換字串(new_str)	ü (選擇性參數，如省略代表空白字元)	ü

-    置換字串(new_str)

兩者均可選擇指定置換新字串(new_str)，ORACLE為選擇性參數，如省略則預設為空白字元。

TRANSLATE指令比較:

將字串(str)依from與to間以字元一對一對應關係進行取代。。

	ORACLE	MSSQL
語法	TRANSLATE(str, from, to)	TRANSLATE(str, from, to)
對應字串(from)	ü	ü
對應字串(to)	ü (字元數目允許與from不同，當無法對應者將被剔除)	ü (字元數目必需與from相同)

以下列出REPLACE及TRANSLATE使用時機，如下表所示。

函數	適用時機	說明
TRANSLATE	多個單一字元轉換、剔除 如：剔除所有數值	若以REPLACE函數則需採用巢狀呼叫，逐一將各字元轉換或剔除
REPLACE	字元組（子串）轉換、剔除 如：AB→X	若視字元組為一組（SET），則採用REPLACE。TRANSLATE僅能處理單一字元對應轉換，無法處理字元組轉換

範例:

	ORACLE	MSSQL
將AN置換成XY BXYXYA	SELECT REPLACE('BANANA', 'AN', 'XY') FROM DUAL	SELECT REPLACE('BANANA', 'AN', 'XY')
將AN置換成空白字元 BA	SELECT REPLACE('BANANA', 'AN') FROM DUAL	SELECT REPLACE('BANANA', 'AN', '')
依A=>X, N=>Y對應轉換 BXYXYX	SELECT TRANSLATE ('BANANA ', 'AN', 'XY') FROM DUAL	SELECT TRANSLATE ('BANANA', 'AN', 'XY') (2017版)
從位置2起移除4個字元 再以X填入 BXA	SELECT REGEXP_REPLACE('BANANA'           , '(^.{1})(.{4})(.*)$' , '\1X\3') FROM DUAL	SELECT STUFF('BANANA', 2, 4, 'X')

由(1)、(3)結果可得REPLACE及TRANSLATE函數的不同。

以下函數相對複雜，請參閱延伸文章：

函數	資料庫	延伸閱讀
STUFF	MSSSQL	[MSSQL] STUFF字串置換函數使用方法及範圍
TRANSLATE	ORACLE/MSSQL2017	解析TRANSLATE字元置換(取代)函數
REGEXP_REPLACE	ORACLE	[ORACLE] 正規表示式應用-REGEXP_REPLACE函數