CODEWITHSUNDEEP
mysqlsql
Rocco The Taco
Rocco The Taco

Reputation: 3797

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?

Upvotes: 86

Views: 291921

Answers (4)

ganji
ganji

Reputation: 864

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

Upvotes: 2

raina77ow
raina77ow

Reputation: 106483

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.

Upvotes: 173

sung
sung

Reputation: 366

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

Upvotes: 10

GamerJosh
GamerJosh

Reputation: 3647

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

Upvotes: 8

Related Questions