There are many days in the life of a programmer, when you need to build a search query based on the some/all fields of user input. This function would return the first non-null value.
Coalesce function could be a perfect fit in such cases. It could replace case statements, ISNULL etc.
The other beauty of Coalesce is it take multiple values, whereas ISNULL can take only two values to compare.
SELECT Name, COALESCE(HomePhone, Mobile, Pager) AS ContactNumber FROM User
would return the first non-null value or null value if all of the values are nullPlease follow here for much more on this.
Coalesce on SqlTeam
Interesting Facts on Coalesce