al-yemen al-saeed al-yemen al-saeed - 5 months ago 9
MySQL Question

how to merge column1 with columns2 in database with sql

I want to merge 2 columns as 1 but not as UNION does but I want to merge this :

Id | FirstName | LastName
-------------------------
1 | Madian | Aziz
2 | Jim | Gordon
-------------------------


Into this :

Id | FullName
-----------------
1 | Madian Aziz
2 | Jim Gordon
-----------------


with one space " " between the 2 columns.

NOTE: THE 2 COLUMNS ARE IN THE SAME TABLE AND THEY HAVE THE SAME ID

Answer

You are looking for concat(). I would use concat_ws():

select id, concat_ws(' ', firstname, lastname) as fullname
from t;

The nice thing about concat_ws() is that it handles NULL values in either column.

EDIT:

You can put the above logic in a view, if you want to fetch the results.

Although you can add a full name column:

alter table add fullname varchar(255);

update t
    set fullname = concat_ws(' ', firstname, lastname);

However, MySQL does not let you keep the columns in sync. Other databases support a construct called computed columns, but not MySQL. Your best bet is to use a view for accessing the table.