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?
You do not need to select the columns separately in order to use them in your CONCAT. Simply remove them, and your query will become:
SELECT FirstName AS First_Name
, LastName AS Last_Name
, CONCAT(ContactPhoneAreaCode1, ContactPhoneNumber1) AS Contact_Phone
FROM TABLE1
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
In case of NULL
columns it is better to use IF
clause like this which combine the two functions of : CONCAT
and COALESCE
and uses special chars between the columns in result like space or '_'
SELECT FirstName , LastName ,
IF(FirstName IS NULL AND LastName IS NULL, NULL,' _ ',CONCAT(COALESCE(FirstName ,''), COALESCE(LastName ,'')))
AS Contact_Phone FROM TABLE1
Source: Stackoverflow.com