Oracle 中 replace函數和translate函數比較

簡要比較：
    replace：字串層級的代替
 
    如：SELECT REPLACE('acdd','cd','ef') FROM dual; →aefd
 
    translate：字元層級的代替
 
    如：SELECT TRANSLATE('acdd','cd','ef') FROM dual; →aeff
 
    分別詳解：
 
    replace:
 
    文法：REPLACE ( char , search_string [, replacement_string] )
 
    REPLACE returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed. If search_string is null, then char is returned.
 
    解釋：repalce中，每個search_string都被replacement_string所代替。
 
    select replace('acdd','cd','ef') from dual;→ aefd
 
    如果replacement_string為空白或為NULL,那麼所有的search_string都被移除。
 
    select replace('acdd','cd','') from dual;→ad
 
    如果search_string為null,那麼就返回原來的char。
 
    select replace('acdd','','ef') from dual;→acdd
 
    select replace('acdd','','') from dual;→acdd(也是兩者都為空白的情況)
 
    Both search_string and replacement_string, as well as char, can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as char.
 
    解釋：這段指明了可以用的資料類型。
 
    This function provides functionality related to that provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings.
 
    解釋：紅色部分也是replace和translate的區別。

 

 translate:
    文法：TRANSLATE ( 'char' , 'from_string' , 'to_string' )
 
    TRANSLATE returns char with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in char that are not in from_string are not replaced. The argument from_string can contain more characters than
to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in char, then they are removed from the return value.You cannot use an empty string for to_string to remove
all characters in from_string from the return value. Oracle interprets the empty string as null, and if this function has a null argument, then it returns null.
 
    解釋：Translate中，每個from_string中的字元被to_string中相應的字元所代替。
 
    select translate('acdd','cd','ef') from dual;→aeff
 
    如果from_string比to_string長，那麼from_string中多餘的字元將被移除。
 
    select translate('acdd','acd','ef') from dual;→ef (a由e代替，c由f代替,d就被移除)
 
    select translate('acdd','cda','ef') from dual;→eff(c由e代替，d由f代替，a就被移除)
 
    如果to_string為空白，或者兩者都為空白，那麼返回char也為空白。所以to_string不可為空。
 
    select translate('acdd','cd','') from dual;→ (空)
 
    select translate('acdd','','') from dual;→(空)
 
    實戰：
 
    如何判斷一個字串是否是數字？
 
    解：先轉換：由於to_string不可為空，我們巧用#號代替
 
    select translate('abc123','#1234567890.','#') from dual;→abc
 
    from_string中的#被to_string中的#代替，但char中又沒有#字元，所以通過這一步躲開了to_string必須不為空白的規則。然後後面的數字以及小數點都轉換為空白，於是原來的字串中只留下abc三個字元。
 
    轉換好後，用nvl2判斷即可：
 
    select nvl2(translate('abc123','#1234567890.','#'),'字串','數字') from dual;→字串
 
    nvl2的作用就是，NVL2 (expr1, expr2, expr3) ->expr1不為NULL，返回expr2；為NULL，返回expr3。這樣我們就可以判斷一個字串是否是數字了!解畢!