select lmark3, translate( t.lmark3, '9876543210' ||t.lmark3, '9876543210')from line_data_all_t t
1
數學一：51
51
2
數學三：100
100
3
大學實體131
131
4
法律碩士聯考專業基礎115
115
5
數學：191
191
6
國際政治121
121
7
管理學：110
110
8
西醫綜合155
155
9
護理綜合217
217
10
設計學概論130
130
11
西醫綜合172
172
12
數學：45
45
13
數學：1110
1110
1.translate
文法：TRANSLATE(char, from, to)
用法：傳回将出現在from中的每個字元替換為to中的相應字元以後的字元串。
若from比to字元串長，那麼在from中比to中多出的字元将會被删除。
三個參數中有一個是空，傳回值也将是空值。
舉例：SQL> select translate('abcdefga','abc','wo') 傳回值 from dual;
傳回值
-------
wodefgw
分析：該語句要将'abcdefga'中的'abc'轉換為'wo',
由于'abc'中'a'對應'wo'中的'w',
故将'abcdefga'中的'a'全部轉換成'w';
而'abc'中'b'對應'wo'中的'o',
故将'abcdefga'中的'b'全部轉換成'o';
'abc'中的'c'在'wo'中沒有與之對應的字元,
故将'abcdefga'中的'c'全部删除;
簡單說來，就是将from中的字元轉換為to中與之位置對應的字元，
若to中找不到與之對應的字元，傳回值中的該字元将會被删除。
在實際的業務中，可以用來删除一些異常資料，
比如表a中的一個字段t_no表示電話号碼，
而電話号碼本身應該是一個由數字組成的字元串，
為了删除那些含有非數字的異常資料，
就用到了translate函數：
SQL> delete from a，
where length(translate(trim(a.t_no),
'0123456789' || a.t_no,
'0123456789')) <> length(trim(a.t_no));
2.replace
文法：REPLACE(char, search_string,replacement_string)
用法：将char中的字元串search_string全部轉換為字元串replacement_string。
舉例：SQL> select REPLACE('fgsgswsgs', 'fk' ,'j') 傳回值 from dual;
---------
fgsgswsgs
SQL> select REPLACE('fgsgswsgs', 'sg' ,'eeerrrttt') 傳回值 from dual;
-----------------------
fgeeerrrtttsweeerrrttts
分析：第一個例子中由于'fgsgswsgs'中沒有與'fk'比對的字元串，
故傳回值仍然是'fgsgswsgs'；
第二個例子中将'fgsgswsgs'中的字元串'sg'全部轉換為'eeerrrttt'。
總結：綜上所述，replace與translate都是替代函數，
隻不過replace針對的是字元串，而translate針對的是單個字元。
*************************************第二篇介紹說明推薦，詳細說明介紹***************************************簡要比較：
replace 字元串級别的代替
如：SELECT REPLACE('accd','cd','ef') from dual; –> acef
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中
舉例說明：
SELECT TRANSLATE('abcdefghij','abcdef','123456') FROM dual; TRANSLATE (
--------------
123456ghij
SELECT TRANSLATE('abcdefghij','abcdefghij','123456') FROM dual; TRANSL
----------
123456
select TRANSLATE('kkaxksx', 'kx', '12') from dual
結果：11a21s2
translate中有“#”的特殊用法，以#開頭的表示所有字元
translate的主要作用是提取,替換字元串,其作用有時候和replace差不多.具體看下面的例子
select translate('liyan4h123ui','#liyanhui','#') from dual
結果：4123
select translate('liyan4h123ui','#liyanhui','#z') from dual;
結果：z4123
select translate('liyan4h123ui','#liyanhui','#zx') from dual;
結果：zx4123x
select translate('asadad434323', '#0123456789','#') from dual ;
結果：asadad
利用TRANSLATE實作關鍵字的過濾
有時候需要對一些關鍵詞語進行過濾，直接使用replace的話，可能由于這些關鍵詞語比較多而要嵌套使用，語句也不好寫，同時也浪費資源。這種情況其實可以使用TRANSLATE和replace組合使用就能完全達到目的了。
比如要将“深圳”、“北京”等作為關鍵詞語，在顯示内容是要将這些詞語過濾掉不顯示：
--首先使用TRANSLATE将關鍵詞語統一轉換成一個特殊的字元串，比如這裡的X
SQL> select TRANSLATE('上海北京天津重慶廣州深圳武漢','深圳北京','XXXX') from dual;
TRANSLATE('上海北京天津重慶廣?
------------------------------
上海XX天津重慶廣州XX武漢
--然後用replace将特殊的字元串替換掉。注意：不能用TRANSLATE直接将關鍵詞語直接轉換為''字元串
SQL> select replace(TRANSLATE('上海北京天津重慶廣州深圳武漢','深圳北京','XXXX'),'X') from dual;
REPLACE(TRANSLATE('上海北京天?
上海天津重慶廣州武漢
SQL> --但是，用TRANSLATE是以一個字元為機關的，隻要比對到都會轉換。比如不管“北”和“京”是否連接配接在一起都會做轉換
SQL> select TRANSLATE('上海京天津重慶北廣州深圳武漢','深圳北京','XXXX') from dual;
TRANSLATE('上海京天津重慶北廣?
上海X天津重慶X廣州XX武漢
補充：TRANSLATE(string,from,to)轉換的兩個注意點——
1、轉換源字串(from)在目的字串(to)中不存在對應，則轉換後被截除
2、轉換目的字串(to)不能為''，''在oracle中被視為空值，是以無法比對而傳回為空值
另外，一個漢字作為一個字元還是兩個字元進行轉換與字元集的設定相關。
**********************
本文轉自hcy's workbench部落格園部落格，原文連結：<b>http://www.cnblogs.com/alterhu/archive/2012/04/06/2434280.html</b>，如需轉載請自行聯系原作者。
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)