Home > Database > Mysql Tutorial > oracle 处理空值的函数

oracle 处理空值的函数

WBOY
Release: 2016-06-07 15:36:43
Original
1946 people have browsed it

1、nvl(expr1,expr2) 如exp1是空,则返回exp2,否则返回expr1; 2、nvl2(expr1,expr2,expr3) 如果exp1是空,则返回expr3,否则返回expr2; 3、coalesce(expr[,expr1]...) 返回参数里面第一个非空; with test as ( select 'c11' col_1, '' col_2, 'c31' col_3 fro

1、nvl(expr1,expr2)

如exp1是空值,则返回exp2,否则返回expr1;

2、nvl2(expr1,expr2,expr3)

如果exp1是空值,则返回expr3,否则返回expr2;

3、coalesce(expr[,expr1]...)

返回参数里面第一个非空值;

 

with test as
( select 'c11' col_1, '' col_2, 'c31' col_3 from dual union all
  select '' col_1, 'c21' col_2, 'c32' col_3 from dual union all
  select 'c13' col_1, 'c22' col_2, '' col_3 from dual union all
  select '' col_1, 'c23' col_2, 'c33' col_3 from dual union all
  select 'c14' col_1, '' col_2, 'c34' col_3 from dual union all
  select 'c15' col_1, '' col_2, '' col_3 from dual
)
select col_1, nvl(col_1, col_1) exp_1,
       col_2, nvl2(col_2,col_2||',','is null') exp_2,
       col_3, coalesce(col_1, col_2, col_3) exp_3
from test;

 

COL_1     EXP_1 COL_2   EXP_2    COL_3  EXP_3
--------- ----- ------- -------  -----  -----
c11       c11           is null  c31    c11
                c21     c21,     c32    c21
c13       c13   c22     c22,            c13
                c23     c23,     c33    c23
c14       c14           is null  c34    c14
c15       c15           is null         c15
 
6 rows selected

 


 

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template