Coalesce Function

September 9th, 2006 by Rajender Singh · No Comments

Today I just come across 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 )

In above case It will first check expr1 whether its null or not, if not null then it will return its value, if null then will skip to next parameter expr2, it will try to do same thing with expr2 and if null then will skip to next parameter untill expr_n. If all the column have null value then it will return null.

Tip: Can be use instead of CASE or DECODE function, only if column invloved stores either null (where value not required not ‘0′ or space) or non null value (where value required).