Open In App

How to Concat Two Columns Into One With the Existing Column Name in MySQL?

Improve
Improve
Like Article
Like
Save
Share
Report

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.

Syntax:

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.

Query:

CREATE DATABASE geeks;

Step 2: Use database

Use the below SQL statement to switch the database context to geeks:

Query:

USE geeks;

Step 3: Table definition

We have demo_table in our geek’s database.

Query:

CREATE TABLE demo_table(
FIRSTNAME VARCHAR(20),
LASTNAME VARCHAR(20),
AGE INT);

 

Step 4: Insert data into a table

Query:

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

Query:

SELECT * FROM demo_table;

Output:

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.

Query:

SELECT  *, CONCAT(FIRSTNAME, LASTNAME) AS FIRSTNAME
FROM demo_table;

Output:

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.

Query:

SELECT  *, CONCAT(FIRSTNAME,' ', LASTNAME) as FIRSTNAME
FROM demo_table;

Output:

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.

Query:

UPDATE demo_table  
SET FIRSTNAME = REPLACE(FIRSTNAME,FIRSTNAME, CONCAT(FIRSTNAME,' ', LASTNAME));

Output:


Last Updated : 19 Nov, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads