MySQL SELECT AS combine two columns into one

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?

269923 次浏览
小开

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
小开
最佳答案

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.

小开

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

提交答案