2020年5月17日 星期日

解析TRANSLATE字元置換(取代)函數

先前文章《剔除地址中[全形數字]字元》案例中探討如欲將地址中全形數字轉換為半型字,如使用REPLACE字串置換函數,則需以10(0~9)巢狀呼叫方式才可達成,在此詳述ORACLEMSSQL2017所提供另一字元置換函數1TRANSLATE,此函數可依fromto對應關係將字串string中字元逐一轉換對應字元而變成另一字,當fromto間由左至右可建立一對一關係時ORACLEMSSQL兩種資料庫處理結果相同;若無法完整建立對應關係時(fromto字元數不同)則兩者不同,在無法對應ORACLE將以空值代替MSSQL則異常語法如下所示 
TRANSLATE (string, from, to)

以下以「ABANDON」字串為範例資料,將「A」字元置換為「#」以及將「A」字元置換為空字元(即剔除「A」字元)兩個。

範例(#
REPLACE('ABANDON', 'A, '#')
REPLACE('ABANDON', 'A')
SQL語法
TRANSLATE('ABANDON', 'A', '#')
TRANSLATE('ABANDON', '@A', '@') : MSSQL不支援
資料
1
2
3
4
5
6
7
A
B
A
N
D
O
N
1
2
3
4
5
6
7
A
B
A
N
D
O
N
對應表
from
A
to
#
from
@
A
to
@

轉換
0
$
0
,
$
$
$
#
B
#
N
D
O
N
,
$
,
$
$
$
$

B

N
D
O
N
結果
#B#NDON
BNDON
註:
l   未設對應($),即未定義於from參數的字元,將保留轉換前字元。
l   對應轉換(0),定義於fromto參數的可對應字元,將置換成對應字元。
l   單向對應(,),定義於from參數但無法對應於to的字元,將置換為空(即剔除)。

範例1:完整對應(ORACLE均支援/ MSSQL )
將「A」字元置換為「#」,由於是一對一轉換關係,因此將「A」、「#」分別置於fromto參數中,因此,所有的「A」字元均將被置換為「#」,轉換過程及結果如上表所示。

範例2: 部分對應(ORACLE支援/ MSSQL不支援)
將「A」字元置換為空白字元(即剔除「A」字元),為使TRANSLATE函數有效被應用,to參數不得為空字元,因此將建立單向對應關係,from參數為@A」而to參數為「@(僅是為符合限制條件所做),對應關係表如下所示。
from
@
A
to
@


前述將建立「A」字元單向對應關係,對於定義於from參數但無法對應至to參數的字元,將被置換為空字元(即剔除),由於MSSQL不支援此作法,如仍欲達成相同需求,則可用特殊符號如CHAR(10)CHAR(13)to參數中建立與from參數數量相等之對應字串,以巢狀呼叫REPLACE函數將中介處理之特殊符號轉換成空值即可解決。


如同另一篇《剔除地址中[全形數字]字元》案例所探討,如欲將全形數字全部剔除時受限於MSSQL轉換對應參數from / to需一對一完全對應(字元數量相同)SQL語法修正如下
SELECT Addr
       --, TRANSLATE(Addr, '@0123456789', '@')--ORACLE才支援
       , REPLACE(TRANSLATE(Addr, '@0123456789'
, '@' + REPLICATE(CHAR(10), 10)) --造出一對一完全對應關係
, CHAR(10), '')
       , TRANSLATE(Addr, '@0123456789', '@0123456789')     
FROM
      (     
    SELECT N'10090台北市中正區羅斯福路9段1號3樓' Addr
    --FROM DUAL
    UNION ALL
    SELECT N'100台北市中正區幸忠孝東路9段19號4樓'
    --FROM DUAL
    UNION ALL
    SELECT N'台北市中正區忠孝東路5段49號'
    --FROM DUAL
     )  A



綜合前述,TRANSLATE()函數最重要的在於如何建立fromto參數的對應關係,歸納以下兩個特性:
l  參數from與參數to將建構11的關係,若無法對應則傳回空值:如範例將用於剔除字串中的字元A。。
l  參數to不允許為空值:若建立轉換對應至空值的對照設定,請注意此設定是屬合法的語法,但回傳結果永遠都是空值,另外MSSQL需與from參數完整對應之to參數。

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

沒有留言:

張貼留言