常用Oracle函数记录

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

------待续

常用Oracle函数记录

标签：