This article is written about how to use the Oracle/PLSQL COALESCE function with syntax and examples.
Description
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.
Syntax
The syntax for the COALESCE function in Oracle/PLSQL is:
COALESCE( expr1, expr2, ... expr_n )Parameters or Arguments
expr1, expr2, … expr_n
The expressions to test for non-null values. The expressions must all be the same datatype.
Returns
The COALESCE function returns returns any datatype such as a string, numeric, date, etc. (BUT all expressions must be the same datatype in the COALESCE function.)
If all expressions are not the same datatype, an ORA-00932 error will be returned.
Applies To
The COALESCE function can be used in the following versions of Oracle/PLSQL:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i
Example
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.