How to Concat Two Columns Into One With the Existing Column Name in MySQL?
In this article, we will see an SQL query to concatenate two-column into one with the existing column name. We can perform the above activity using the CONCAT() function.
CONCAT(): It takes column names as parameters and returns a column with value after concatenating all the values of the column passed parameters to the function. There are two ways to perform the activity:
- Without replacing the existing column with the CONCAT function.
- By replacing the existing column using REPLACE() function with CONCAT() function.
CONCAT(column_name1, column_name2) AS column_name;
Step 1: Create a database
we can use the following command to create a database called geeks.
CREATE DATABASE geeks;
Step 2: Use database
Use the below SQL statement to switch the database context to geeks:
Step 3: Table definition
We have demo_table in our geek’s database.
CREATE TABLE demo_table( FIRSTNAME VARCHAR(20), LASTNAME VARCHAR(20), AGE INT);
Step 4: Insert data into a table
INSERT INTO demo_table VALUES ('Romy', 'Kumari', 21), ('Pushkar', 'Jha', 22), ('Meenakshi', 'Jha', 19), ('Rinkle', 'Arora', 22), ('Ayushi', 'Choudhary', 21), ('Sujata', 'Jha', 31);
Step 5: View the content
Execute the below query to see the content of the table
SELECT * FROM demo_table;
Step 6: Concatenate two-column into one
Method 1: Without replacing the existing column
This method will not make changes to the original table.
For the demonstration, we will concatenate FIRSTNAME and LASTNAME and will name the column FIRSTNAME.
SELECT *, CONCAT(FIRSTNAME, LASTNAME) AS FIRSTNAME FROM demo_table;
Here, we can see that FIRSTNAME and LASTNAME is concatenated but there is no space between them, If you want to add space between the FIRSTNAME and LASTNAME then add space(‘ ‘) in CONCAT() function.
SELECT *, CONCAT(FIRSTNAME,' ', LASTNAME) as FIRSTNAME FROM demo_table;
Method 2: By replacing the existing column
This method will change the original table.
For the demonstration, we will replace FIRSTNAME with the concatenated value of FIRSTNAME and LASTNAME column.
UPDATE demo_table SET FIRSTNAME = REPLACE(FIRSTNAME,FIRSTNAME, CONCAT(FIRSTNAME,' ', LASTNAME));