This Oracle tutorial explains how to use the Oracle/PLSQL NVL2 function.
NVL2(x, value1, value2) returns value1
if x is not null; otherwise value2 is returned.
The Oracle/PLSQL NVL2 function extends the NVL function.
It can substitute a value for a null value and it also can repalce a non-null value.
The syntax for the Oracle/PLSQL NVL2 function is:
NVL2( val, value_if_NOT_null, value_if_null )
val is the value to test for a null value.
value_if_NOT_null is the value returned if val is not null.
value_if_null is the value returned if val is null.
The following SQL statement returns 'n/a' if the city field contained a null value. Otherwise, it would return the 'Completed'.
select NVL2(city, 'Completed', 'n/a') from emp;
The following SQL statement returns the name2 field if the desc contained a null value. Otherwise, it would return the name field.
select supplier_id, NVL2(desc, name, name2) from suppliers;
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
-- w w w. j ava2 s .co m
INSERT INTO EMP VALUES (1, null, 'CLERK', 800, 20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600, 30);
INSERT INTO EMP VALUES (3, 'WARD', 'SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER', 2975, 20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250, 30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER', 2850, 30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER', 2850, 10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST', 3000, 20);
INSERT INTO EMP VALUES (9, 'KING', 'PRESIDENT',3000, 10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500, 30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK', 1500, 20);
SQL> SELECT empno, NVL2(ename, 'Known', 'Unknown')
2 FROM emp;
EMPNO NVL2(EN
---------- -------
1 Unknown
2 Known
3 Known
4 Known
5 Known
6 Known
7 Known
8 Known
9 Known
10 Known
11 Known
11 rows selected.
SQL>