2020年6月1日 星期一

[字串函數]字串取代

以新字串取代目標字串中的特定(舊)字串,依功能區分成:
(1).符合字串-置換(REPLACE)
(2).字元對應-置換(TRANSLATE)
(2).位置置換/填入/移除(STUFF)MSSQL提供
(4).正規表示式(REGEXP_REPLACE)ORACLE提供

指令說明:
ORACLE: 提供REPLACETRANSLATEREGEXP_REPLACE函數,由於REGEXP_REPLACE相對複雜,將撰專文說明
語法
說明
REPLACE(str, old_str [,new_str])
(str)中所有特定()(old_str) 以新字串(new_str)取代。new_str預設為空字串。
TRANSLATE(str, from, to)
將字串(str)fromto間以字元一對一對應取代。

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)fromto間以字元一對一對應取代。
(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)fromto間以字元一對一對應關係進行取代。
ORACLE
MSSQL
語法
TRANSLATE(str, from, to)
TRANSLATE(str, from, to)
對應字串(from)
ü
ü
對應字串(to)
ü
(字元數目允許與from不同,當無法對應者將被剔除)
ü
(字元數目必需與from相同)

以下列出REPLACETRANSLATE使用機,如下表所示
函數
適用時機
說明
TRANSLATE
多個單一字元轉換、剔除
如:剔除所有數值
若以REPLACE函數則需採用巢狀呼叫,逐一將各字元轉換或剔除
REPLACE
字元組(子串)轉換、剔除
如:AB→X
若視字元組為一組(SET),則採用REPLACETRANSLATE僅能處理單一字元對應轉換,無法處理字元組轉換

範例:

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)結果可得REPLACETRANSLATE函數的不同。

以下函數相對複雜,請參閱延伸文章:
函數
資料庫
延伸閱讀
STUFF
MSSSQL
TRANSLATE
ORACLE/MSSQL2017
REGEXP_REPLACE
ORACLE

沒有留言:

張貼留言