COALESCE function in Oracle

Syntax

The syntax for the COALESCE function is:

COALESCE( expr1, expr2, ... expr_n )

The COALESCE function can be used in Oracle/PLSQL.

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

SELECT COALESCE( address1, address2, address3 ) result

FROM suppliers;

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;