Coalesce example

Question

hi frnds can any 1 tell me what is coalesce and how we can use it in our Sql Query. pls give an example. Thanks & Regards Aamir

Satyapriya Nayak · Answer

Hi Aamir The COALESCE function in SQL returns the first non-NULL expression among its arguments. It is the same as the following CASE statement: SELECT CASE (column_name) WHEN expression 1 is not NULL THEN expression 1 WHEN expression 2 is not NULL THEN expression 2 ... [ELSE NULL] END FROM table_name For examples say we have the following table Table Contact_Info Name Business_Phone Cell_Phone Home_Phone Jeff 531-2531 622-7813 565-9901 Laura NULL 772-5588 312-4088 Peter NULL NULL 594-7477 and we want to find out the best way to contact each person according to the following rules: 1. If a person has a business phone use the business phone number. 2. If a person does not have a business phone and has a cell phone use the cell phone number. 3. If a person does not have a business phone does not have a cell phone and has a home phone use the home phone number. We can use the COALESCE function to achieve our goal: SELECT Name COALESCE(Business_Phone Cell_Phone Home_Phone) Contact_Phone FROM Contact_Info; Result: Name Contact_Phone Jeff 531-2531 Laura 772-5588 Peter 594-7477 refer http://www.1keydata.com/sql/sql-coalesce.html Thanks