A chronological documentation test project, nothing serious, really!

06 Sep 2009 Howto combine two columns into one in mysql

Posted by

This is how you can combine (or concatenate or merge) data from two table columns into one column using a mysql SQL query.

SELECT CONCAT(firstname,lastname) AS 'name' FROM tablename

The result would be “firstname lastname”

If you would like to insert this data into a new column

INSERT INTO tablename (full_name) SELECT CONCAT(firstname,lastname) AS 'name' FROM tablename

Tags: , ,

Reader's Comments

  1. |

    I needed something similar with new lines (\r\n) as well, while properly removing blank sections. This was what I used…

    INSERT INTO tablename (full_name) SELECT REPLACE(REPLACE(REPLACE(CONCAT(“XXXXXXXX”,firstname,”XXXXXXXX”,”\r\n”,”XXXXXXXX”,midname,”XXXXXXXX”,”\r\n”,”XXXXXXXX”,lastname,”XXXXXXXX”),”XXXXXXXXXXXXXXXX\r\n”,””),”\r\nXXXXXXXXXXXXXXXX”,””),”XXXXXXXX”,””) AS ‘name’ FROM tablename;

    1st step: CONCAT(“XXXXXXXX”,firstname,”XXXXXXXX”,”\r\n”,”XXXXXXXX”,midname,”XXXXXXXX”,”\r\n”,”XXXXXXXX”,lastname,”XXXXXXXX”)
    Concat with markers (“XXXXXXXX” in this case) and newline characters with carriage return \r and line feed \n.

    2nd step: REPLACE([1st step results], “XXXXXXXXXXXXXXXX\r\n”, “”)
    Remove empty sections by spotting 2 of our markers together.

    3rd step: REPLACE([2nd step results], “\r\nXXXXXXXXXXXXXXXX”, “”)
    Check for 2 of our markers together again the opposing direction.

    4rd step: REPLACE([3rd step results], “XXXXXXXX”, “”)
    Clean up our markers.

    5th step: INSERT INTO tablename (full_name) SELECT [4th step results] AS ‘name’ FROM tablename
    Insert into the table.