Oracle PL/SQL: Coalesce Function

Oracle PL/SQL: Coalesce Function

In Oracle/PLSQL, the coalesce function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function will return null.


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.

Applies To

  • Oracle 11g, Oracle 10g, Oracle 9i

For Example

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

SELECT coalesce( address1, address2, address3 ) result
FROM suppliers;
The above coalesce statement 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;

   result := null;

The coalesce function will compare each value, one by one.

Post a Comment