How to Concat Two Columns Into One in MySQL?
Concatenating columns in MySQL is a key operation for combining data from multiple fields into a single, unified column. This process is essential for generating comprehensive reports and merging data elements like names or addresses.
This article explores two effective methods for column concatenation using the CONCAT() function and the REPLACE() function.
How to Concatenate Two Columns Using MySQL?
Concatenating columns is a common requirement in data manipulation and reporting. We might need to combine first names and last names into a full name or merge address components into a single address field.MySQL provides multiple approaches to achieve this, each with its use cases and benefits.
Let's set up an Environemnt
Demo Table
CREATE DATABASE geeks;
USE geeks;
CREATE TABLE demo_table(
FIRSTNAME VARCHAR(20),
LASTNAME VARCHAR(20),
AGE INT);
INSERT INTO demo_table VALUES
('Romy', 'Kumari', 21),
('Pushkar', 'Jha', 22),
('Meenakshi', 'Jha', 19),
('Rinkle', 'Arora', 22),
('Ayushi', 'Choudhary', 21),
('Sujata', 'Jha', 31);
Output:
There are two methods to concat columns in MySQL, depending on your use case, you can use any of these techniques to concat two columns.
Method 1: Using the CONCAT Function
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: Using the REPLACE Function
This method will change the original table.
For the demonstration, we will replace FIRSTNAME with the concatenated value of FIRSTNAME and LASTNAME column. To do this, we can use REPLACE function with CONCAT function.
Query:
UPDATE demo_table
SET FIRSTNAME = REPLACE(FIRSTNAME,FIRSTNAME, CONCAT(FIRSTNAME,' ', LASTNAME));
Output:
Conclusion
MySQL offers flexible methods for concatenating columns, with CONCAT() being ideal for temporary views and reports, while REPLACE() is useful for permanent data updates. Understanding these techniques allows you to efficiently manage and manipulate your data according to your specific needs.
