COALESCE

COALESCE

COALESCE function, a more generic form of NVL, returns the first non-null expression in the argument list. It takes minimum two mandatory parameters but maximum arguments has no limit.

Syntax

COALESCE (expr1, expr2, ... expr_n )
Consider the below SELECT query. It selects the first not null value fed into address fields for an employee.
SELECT COALESCE (address1, address2, address3) Address
FROM  employees;
The working of COALESCE function is similar to IF..ELSIF..ENDIF construct. The query above can be re-written as:
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;
Read more: Conv. Func.

Follow CodeGalaxy

Mobile Beta

Get it on Google Play
Send Feedback
Keep exploring
SQL snippets
Cosmo
Sign Up Now
or Subscribe for future quizzes