Oracle Conversion Function - Oracle/PLSQL COALESCE Function

This Oracle tutorial explains how to use the Oracle/PLSQL COALESCE function.

The coalesce function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function returns null.

Syntax

The syntax for the coalesce function is:

coalesce( expr1, expr2, ... expr_n )

expr1 to expr_n are the expressions to test for non-null values.

Example

You can use the coalesce function in an SQL statement as follows:


SQL> SELECT coalesce( null, '1', null,'2') result FROM dual;
-- from ww w .  ja v  a 2  s . c  o m
R
-
1

SQL> SELECT coalesce( null, null, null,'2') result FROM dual;

R
-
2

SQL>

COALESCE several columns

The following code shows how to get a non-null columns.

SELECT COALESCE( address1, address2, address3 ) result
 FROM employee;

The above COALESCE function is equivalent to the following IF-THEN-ELSE statement:

IF address1 is not null THEN
    result := address1;
 
 ELSIF address2 is not null THEN
    result := address2;
 
 ELSIF address3 is not null THEN
    result := address3;
 
 ELSE
    result := null;
 
END IF;