How to Concatenate Two Columns in MySQL

1. Concatenate Columns Using the CONCAT Function in MySQL
2. Concatenate Columns Using the CONCAT_WS Function in MySQL

This article will show you various ways to concatenate data of two columns in MySQL. This task can easily be accomplished pro-grammatically by selecting fields from a MySQL table and storing their values into one variable after concatenating their values.

It is possible to simplify the above procedure by concatenating the values as you select rows from the Database Table.

Let us now take a simple example. If you have two separate columns in the database as first name and last name and want to show value in both the columns as a single string full name, you can use one of the two given approaches to accomplish your task. Similarly, if an address is split into multiple columns in a database and you want it as a single address, including city, state, and country in your application, then the CONCAT function would be very useful.

Two different functions can be used to accomplish this task.

1. Using the CONCAT function
2. Using the CONCAT_WS function

Both CONCAT() and CONCAT_WS() functions concatenate two or more strings. These two functions differ because the CONCAT_WS() function works along with a separator between strings, whereas the CONCAT() function does not allow the use of separators. There is another significant difference between these functions in that the CONCAT() function returns the NULL value if any of the arguments are NULL. In contrast, the CONCAT_WS() function returns the NULL value only if the separator is NULL.

The following script creates a table student with four columns (sid, firstname, lastname, and email).

CREATE TABLE student (sid INT, firstname VARCHAR(20) NOT NULL, lastname VARCHAR(20) NOT NULL, email VARCHAR(55) NOT NULL);

Here, we insert sample data values into the student table for the demonstration.

INSERT INTO student 
VALUES (111,'Sim','Marlw','sim.marlw@gmail.com'),
(124,'John','Carl','John.carl@gmail.com'),
(362,'Paul','cohelo','paul.coh@google.com'),
(244,'Lunas','sen','Lonas.sen@max.com');

INSERT INTO student 
VALUES (114,'Jaine','Kora','jaine.kora@abs.com'),
(615,'Roma','Sholy','roma.sh11@yahoo.com'),
(997,'Beaon','shrlon','beatrice.ss22@yahoo.com'),
(332,'Peter','cohelo','peter.coh@google.com');

Below given query returns all data from the student table:

SELECT * FROM student order by sid; 

Concatenate Columns Using the CONCAT Function in MySQL

The CONCAT function can concatenate or combine values from multiple columns into a single column. The syntax for the CONCAT function is as shown below:

CONCAT(Column 1, column 2,......)

We will now see how to use this with the SELECT query:

SELECT sid, CONCAT(firstname, " ", lastname) AS fullname FROM student;

The output will be:

Concatenate Columns Using the CONCAT_WS Function in MySQL

The CONCAT_WS function can also concatenate or combine values from multiple columns into single columns. It has an additional feature to add separators along with column values or strings. This separator can be a comma(,), hyphen(-), underscore(_), or any static string or pattern like (***), etc. The syntax for a CONCAT_WS function is as shown below:

CONCAT_WS(SEPARATOR,Column 1, column 2,......)

We will now see how to use this with the SELECT query:

SELECT sid, CONCAT_WS( "_", firstname, lastname,"***" ) AS fullname FROM student;

The output will be: