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

先前文章《剔除地址中[全形數字]字元》案例中探討如欲將地址中全形數字轉換為半型字，如使用REPLACE字串置換函數，則需以10層(0~9)巢狀呼叫方式才可達成，在此詳述ORACLE及MSSQL2017所提供另一字元置換函數1TRANSLATE，此函數可依from與to對應關係將字串string中字元逐一轉換對應字元而變成另一字串，當from與to間由左至右可建立一對一關係時，則ORACLE與MSSQL兩種資料庫處理結果相同；若無法完整建立對應關係時(from與to字元數不同)則兩者不同，在無法對應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），定義於from、to參數的可對應字元，將置換成對應字元。

l   單向對應（,），定義於from參數但無法對應於to的字元，將置換為空（即剔除）。

範例1：完整對應(ORACLE均支援/ MSSQL )

將「A」字元置換為「#」，由於是一對一轉換關係，因此將「A」、「#」分別置於from與to參數中，因此，所有的「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, '@０１２３４５６７８９', '@')--ORACLE才支援        , REPLACE(TRANSLATE(Addr, '@０１２３４５６７８９' , '@' + REPLICATE(CHAR(10), 10)) --造出一對一完全對應關係 , CHAR(10), '')        , TRANSLATE(Addr, '@０１２３４５６７８９', '@0123456789')      FROM       (          SELECT N'１００９０台北市中正區羅斯福路９段１號３樓' Addr     --FROM DUAL     UNION ALL     SELECT N'１００台北市中正區幸忠孝東路９段１９號４樓'     --FROM DUAL     UNION ALL     SELECT N'台北市中正區忠孝東路５段４９號'     --FROM DUAL      )  A

綜合前述，TRANSLATE()函數最重要的在於如何建立from及to參數的對應關係，歸納以下兩個特性：

l  參數from與參數to將建構1對1的關係，若無法對應則傳回空值：如範例將用於剔除字串中的字元「A」。。

l  參數to不允許為空值：若建立轉換對應至空值的對照設定，請注意此設定是屬合法的語法，但回傳結果永遠都是空值，另外MSSQL需與from參數完整對應之to參數。

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

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