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 )

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;

ELSE

    result := null;

END IF;