1. Oracle的replace函数与translate函数
replace函数是在字符串级别的代替,对应字符串一一替换
SQL> SELECT REPLACE(‘accd‘,‘cd‘,‘ef‘) from dual; REPLACE(‘ACCD‘,‘CD‘,‘EF‘) ------------------------- acef
translate函数是在字符级别的代替,对应字符一一替换 translate(expr,from_string,to_string)
SQL> select translate(‘acdd‘,‘cd‘,‘ef‘) from dual; TRANSLATE(‘ACDD‘,‘CD‘,‘EF‘) --------------------------- aeff
translate函数在to_string位置为空,则返回值为空
SQL> select translate(‘acdd‘,‘cd‘,‘‘) from dual; TRANSLATE(‘ACDD‘,‘CD‘,‘‘) -------------------------
translate中有#的特殊用法，以#开头的表示所有字符
SQL> select translate(‘liyan4h123ui‘,‘#liyanhui‘,‘#‘) from dual; TRANSLATE(‘LIYAN4H123UI‘,‘#LIY ------------------------------ 4123 SQL> select translate(‘liyan4h123ui‘,‘#liyanhui‘,‘#z‘) from dual; TRANSLATE(‘LIYAN4H123UI‘,‘#LIY ------------------------------ z4123 SQL> select translate(‘liyan4h123ui‘,‘#liyanhui‘,‘#zx‘) from dual; TRANSLATE(‘LIYAN4H123UI‘,‘#LIY ------------------------------ zx4123x SQL> select translate(‘asadad434323‘, ‘#0123456789‘,‘#‘) from dual; TRANSLATE(‘ASADAD434323‘,‘#012 ------------------------------ asadad
------待续
原文：http://www.cnblogs.com/myrunning/p/4600172.html