sql - MySQL SELECT AS combine two columns into one

Question

Using this solution, I tried to use COALESCE as part of a MySQL query that outputs to a csv file using SELECT As to name the column names when exporting the data.

SELECT FirstName AS First_Name
     , LastName AS Last_Name
     , ContactPhoneAreaCode1
     , ContactPhoneNumber1
     , COALESCE(ContactPhoneAreaCode1, ContactPhoneNumber1) AS Contact_Phone 
  FROM TABLE1

I wanted 3 columns: First_Name, Last_Name and Contact_Phone

I am getting 5 columns: First_Name, Last_Name, ContactPhoneAreaCode1, ContactPhoneNumber1 and Contact_Phone

How do I hide the merging of ContactPhoneAreaCode1, ContactPhoneNumber1 into a single column for Contact_Phone from within the query?

Answer 1

If both columns can contain NULL, but you still want to merge them to a single string, the easiest solution is to use CONCAT_WS():

SELECT FirstName AS First_Name
     , LastName AS Last_Name
     , CONCAT_WS('', ContactPhoneAreaCode1, ContactPhoneNumber1) AS Contact_Phone 
  FROM TABLE1

This way you won't have to check for NULL-ness of each column separately.

Alternatively, if both columns are actually defined as NOT NULL, CONCAT() will be quite enough:

SELECT FirstName AS First_Name
     , LastName AS Last_Name
     , CONCAT(ContactPhoneAreaCode1, ContactPhoneNumber1) AS Contact_Phone 
  FROM TABLE1

As for COALESCE, it's a bit different beast: given the list of arguments, it returns the first that's not NULL.