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?
解决方案
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.