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.

Answer 2

You don't need to list ContactPhoneAreaCode1 and ContactPhoneNumber1

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