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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] MySQL SELECT AS combine two columns into one

Examples related to mysql

Examples related to sql