(轉) Oracle的Replace函數與translate函數詳解與比較

簡要比較：

      replace 字元串級别的代替

     如：SELECT REPLACE('accd','cd','ef') from dual; --> aefd

     translate 字元級别的代替

     如：select translate('acdd','cd','ef') from dual; -->aeff

分别詳解

     replace：

     文法：REPLACE(char,search_string[,replacement_string])

     解釋：replace中，每個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(也是兩者都為空的情況)

      translate:

      文法：TRANSLATE('char','from_string','to_string')

      解釋：translate中，每個from_string中的字元被to_string中

        舉例說明：

Sql代碼  

1. SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual; TRANSLATE (   
2. --------------   
3. 123456ghij   
4. SELECT TRANSLATE('abcdefghij','abcdefghij','123456') FROM dual; TRANSL  
5. ----------   
6. 123456   

Sql代碼  

1. select TRANSLATE('kkaxksx', 'kx', '12') from dual   
2. 結果：11a21s2  

translate中有“#”的特殊用法，以#開頭的表示所有字元

translate的主要作用是提取,替換字元串,其作用有時候和replace差不多.具體看下面的例子

Sql代碼  

1. select translate('liyan4h123ui','#liyanhui','#') from dual   
2. 結果：4123   
3. select translate('liyan4h123ui','#liyanhui','#z') from dual;   
4. 結果：z4123   
5. select translate('liyan4h123ui','#liyanhui','#zx') from dual;   
6. 結果：zx4123x   
7. select translate('asadad434323', '#0123456789','#') from dual ;  
8. 結果：asadad  

利用TRANSLATE實作關鍵字的過濾

有時候需要對一些關鍵詞語進行過濾，直接使用replace的話，可能由于這些關鍵詞語比較多而要嵌套使用，語句也不好寫，同時也浪費資源。這種情況其實可以使用TRANSLATE和replace組合使用就能完全達到目的了。

比如要将“深圳”、“北京”等作為關鍵詞語，在顯示内容是要将這些詞語過濾掉不顯示：

Sql代碼  

1. --首先使用TRANSLATE将關鍵詞語統一轉換成一個特殊的字元串，比如這裡的X   
2. SQL> select TRANSLATE('上海北京天津重慶廣州深圳武漢','深圳北京','XXXX') from dual;   
3. TRANSLATE('上海北京天津重慶廣?   
4. ------------------------------   
5. 上海XX天津重慶廣州XX武漢   
6. --然後用replace将特殊的字元串替換掉。注意：不能用TRANSLATE直接将關鍵詞語直接轉換為''字元串   
7. SQL> select replace(TRANSLATE('上海北京天津重慶廣州深圳武漢','深圳北京','XXXX'),'X') from dual;   
8. REPLACE(TRANSLATE('上海北京天?   
9. ------------------------------   
10. 上海天津重慶廣州武漢   
11. SQL> --但是，用TRANSLATE是以一個字元為機關的，隻要比對到都會轉換。比如不管“北”和“京”是否連接配接在一起都會做轉換   
12. SQL> select TRANSLATE('上海京天津重慶北廣州深圳武漢','深圳北京','XXXX') from dual;   
13. TRANSLATE('上海京天津重慶北廣?   
14. ------------------------------   
15. 上海X天津重慶X廣州XX武漢   

補充：TRANSLATE(string,from,to)轉換的兩個注意點——

1、轉換源字串(from)在目的字串(to)中不存在對應，則轉換後被截除

2、轉換目的字串(to)不能為''，''在oracle中被視為空值，是以無法比對而傳回為空值

另外，一個漢字作為一個字元還是兩個字元進行轉換與字元集的設定相關。