常用Oracle函数记录

 

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

 

 

2. Oracle的nvl函数与coalesce函数

  nvl函数与coalesce函数都是处理null值的函数

  不同是nvl函数只支持两个参数，coalesce函数可以支持多个参数，效率更高

  他们都是返回第一个不为null的值

 

SQL> select nvl('beijing','shanghai') from dual;
 
NVL('BEIJING','SHANGHAI')
-------------------------
beijing
 
SQL> select nvl(null,'shanghai') from dual;
 
NVL(NULL,'SHANGHAI')
--------------------
shanghai
 
SQL> select coalesce('beijing','shanghai','shandong') from dual;
 
COALESCE('BEIJING','SHANGHAI',
------------------------------
beijing 
 
SQL> select coalesce(null,null,'shandong') from dual;
 
COALESCE(NULL,NULL,'SHANDONG')
------------------------------
shandong

 

 

 

 

 

 

------待续