Learn how to use the Oracle/PLSQL COALESCE function with syntax and examples.
The Oracle/PLSQL 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 Oracle/PLSQL COALESCE function is:
COALESCE( expr1, expr2, ... expr_n )
expr1 to expr_n are the expressions to test for non-null values.
The COALESCE function can be used in the following versions of Oracle/PLSQL:
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;
The COALESCE function will compare each value, one by one.