Admin
Hi, In this you are going to learn everything about how to concatenate multiple columns in MySQL. Throughout we are about to explore various ways to concatenate columns in MySQL so that you use any one of them at your convenience.
Basically, Columns concatenation is a mechanism of adding two or more columns together by a separator or not. To concatenate columns in MySQL, Mysql offers two built-in functions which are CONCAT() and CONCAT_WS(), here by saying built-in functions mean it comes with MySQL server by default.
column concatenation in MySQL is a very easy way in comparison to other RDBMS like Microsoft SQL server and PostgreSQL because those RDBMS use + and || operator to add strings.
MySQL uses just two functions CONCAT() and CONCAT_WS() and returns the concatenated version of the value of the columns.
let’s understand both functions along with examples.
Note:- You have to remember one thing always, To perform all these examples firstly you have to loging into mysql client or you can use workbench as well. To login into Mysql client. Just open your MySQL client and use these command mysql -u root -p where root represents the name of the MySQL user.If you want to use any remote MySQL then you can use -h hostname option in this command like mysql -u root -p -h hostname.
Headings of Contents
MySQL CONCAT() Function
The CONCAT() function in MySQL is a built-in function that comes with MySQL by default, It can take various columns and return concatenated version of all those column values. The CONCAT() function must require at least one argument otherwise it will raise an error.
The CONCAT() function first convert all the passed argument into string type and then concatenate together. If any passed argument is NULL then it will return Null.
Syntax
CONCAT(argument1, argument2, argument3,.....)Return Value
The CONCAT() function first convert all the passed arguments into string data type and then concatenate all those string together and return the final string.
MySQL CONCAT() Function Examples
Here, in this example section, we are going to see various examples of the CONCAT() function to concatenate two columns in MySQL together.
I have a table called student having some records for demonstration as you can see below. Now I am going to add the first_name and last_name of the student as a full_name.
mysql> select * from student; +---------+------------+-----------+ | roll_no | first_name | last_name | +---------+------------+-----------+ | 1 | Vishvajit | Rao | | 2 | Jainy | Doe | | 3 | Vaishali | Singh | | 4 | Harshita | Kumari | | 5 | Vishal | Rao | | 6 | Karandeep | Singh | +---------+------------+-----------+ 6 rows in set (0.00 sec)
Example: concatenate two columns in MySQL
mysql> select *, CONCAT(s.first_name, ' ', s.last_name) as 'full_name' from student s; +---------+------------+-----------+-----------------+ | roll_no | first_name | last_name | full_name | +---------+------------+-----------+-----------------+ | 1 | Vishvajit | Rao | Vishvajit Rao | | 2 | Jainy | Doe | Jainy Doe | | 3 | Vaishali | Singh | Vaishali Singh | | 4 | Harshita | Kumari | Harshita Kumari | | 5 | Vishal | Rao | Vishal Rao | | 6 | Karandeep | Singh | Karandeep Singh | +---------+------------+-----------+-----------------+ 6 rows in set (0.00 sec)
As you can see in the above example, how we have added two string columns first_name and last_name as full_name.
Example: Concatenate multiple columns in MySQL
In this example, I am going to concatenate multiple columns in a MySQL database table and display a proper message.
mysql> select *, concat('My Name is ', s.first_name, ' ', s.last_name, ' and my roll number is ', s.roll_no) as 'intro' from student s;
+---------+------------+-----------+----------------------------------------------------+
| roll_no | first_name | last_name | intro |
+---------+------------+-----------+----------------------------------------------------+
| 1 | Vishvajit | Rao | My Name is Vishvajit Rao and my roll number is 1 |
| 2 | Jainy | Doe | My Name is Jainy Doe and my roll number is 2 |
| 3 | Vaishali | Singh | My Name is Vaishali Singh and my roll number is 3 |
| 4 | Harshita | Kumari | My Name is Harshita Kumari and my roll number is 4 |
| 5 | Vishal | Rao | My Name is Vishal Rao and my roll number is 5 |
| 6 | Karandeep | Singh | My Name is Karandeep Singh and my roll number is 6 |
+---------+------------+-----------+----------------------------------------------------+
6 rows in set (0.00 sec)
Same code in MySQL Workbench:
Example: Concatenate columns with null values in MySQL
let’s try to concatenate columns with a Null value, As we know that any value inside CONCAT() function is Null, Then it will return a Null value.
mysql> select *, concat(s.first_name, ' ', s.last_name, Null) as 'full_name' from student s; +---------+------------+-----------+-----------+ | roll_no | first_name | last_name | full_name | +---------+------------+-----------+-----------+ | 1 | Vishvajit | Rao | NULL | | 2 | Jainy | Doe | NULL | | 3 | Vaishali | Singh | NULL | | 4 | Harshita | Kumari | NULL | | 5 | Vishal | Rao | NULL | | 6 | Karandeep | Singh | NULL | +---------+------------+-----------+-----------+ 6 rows in set (0.00 sec)
As you can see we have got a Null value instead of proper full_name.
So, these all are the examples of CONCAT() function along with examples, I hope you will have understood how CONCAT() can be used to concatenate multiple columns in Mysql.
Let’s move on to another one which is the concat_ws() function.
👉 CONCAT() Function Reference:- Click Here
MySQL CONCAT_WS() Function
The CONCAT_WS() is an important function in MySQL to concatenate columns in MySQL together, it is a little bit different from the CONCAT() function because it takes the first argument as a separator.
Syntax
CONCAT_WS(seperator, argument1, argument2,...)Return Value
It returns concatenated strings of passed columns with a passed separator.
MySQL CONCAT_WS() Function Examples
In this example, I am going to use CONCAT_WS() function along with different-different parameters so that you can get more clarity about this.
Example: Concatenate two columns in MySQL using the concat_ws() function
mysql> select *, concat_ws(' ', s.first_name, s.last_name) as 'full_name' from student s;
+---------+------------+-----------+-----------------+
| roll_no | first_name | last_name | full_name |
+---------+------------+-----------+-----------------+
| 1 | Vishvajit | Rao | Vishvajit Rao |
| 2 | Jainy | Doe | Jainy Doe |
| 3 | Vaishali | Singh | Vaishali Singh |
| 4 | Harshita | Kumari | Harshita Kumari |
| 5 | Vishal | Rao | Vishal Rao |
| 6 | Karandeep | Singh | Karandeep Singh |
+---------+------------+-----------+-----------------+
6 rows in set (0.00 sec)
As you can see in the above example, How we have an added first_name and last_name together with separate space (‘ ‘). The first parameter of the concat_ws() function always should be a separator, you can give any separator there.
Let’s use double plus ( ++ ) as a separator.
mysql> select *, concat_ws('++', s.first_name, s.last_name) as 'full_name' from student s;
+---------+------------+-----------+------------------+
| roll_no | first_name | last_name | full_name |
+---------+------------+-----------+------------------+
| 1 | Vishvajit | Rao | Vishvajit++Rao |
| 2 | Jainy | Doe | Jainy++Doe |
| 3 | Vaishali | Singh | Vaishali++Singh |
| 4 | Harshita | Kumari | Harshita++Kumari |
| 5 | Vishal | Rao | Vishal++Rao |
| 6 | Karandeep | Singh | Karandeep++Singh |
+---------+------------+-----------+------------------+
6 rows in set (0.00 sec)
As you can see here, how we have concatenated two words together with a separator double plus sign ( ++ ).
Example: concatenate null values with columns in MySQL using concat_ws()
In this article, I am about to add two columns first_name and first_name with Null values. Let’s see what happens.
mysql> select *, concat_ws(' ', s.first_name, s.last_name, Null) as 'full_name' from student s;
+---------+------------+-----------+-----------------+
| roll_no | first_name | last_name | full_name |
+---------+------------+-----------+-----------------+
| 1 | Vishvajit | Rao | Vishvajit Rao |
| 2 | Jainy | Doe | Jainy Doe |
| 3 | Vaishali | Singh | Vaishali Singh |
| 4 | Harshita | Kumari | Harshita Kumari |
| 5 | Vishal | Rao | Vishal Rao |
| 6 | Karandeep | Singh | Karandeep Singh |
+---------+------------+-----------+-----------------+
6 rows in set (0.00 sec)
As we can see in the above example, When we try to concatenate a Null value with columns, It neglects the Null value and concatenates only valid column values.
So, this is how we can use CONCAT_WS() function to concatenate multiple columns in MySQL.
Example: Concatenate multiple columns in MySQL
👉 CONCAT_WS() Function Reference:- Click Here
Using CONCAT() and CONCAT_WS() function with WHERE clause
The CONCAT() and CONCAT_WS() both functions can be used with the WHERE clause. For instance: we want to fetch only those records whose addition of first_name and last_name is equal to ‘Vishvajit Rao‘.
Example: Using MySQL CONCAT_WS() function with where clause
mysql> select *, concat_ws(' ', s.first_name, s.last_name) as 'full_name' from student s where concat_ws(' ', s.first_name, s.last_name, Null) = 'Vishvajit Rao';
+---------+------------+-----------+---------------+
| roll_no | first_name | last_name | full_name |
+---------+------------+-----------+---------------+
| 1 | Vishvajit | Rao | Vishvajit Rao |
| 7 | Vishvajit | Rao | Vishvajit Rao |
+---------+------------+-----------+---------------+
2 rows in set (0.00 sec)
Example: Using MySQL CONCAT() function with where clause
In this example, I am fetching only those records whose length of full_name ( first_name + last_name ) is less than 12.
SELECT
*, CONCAT_WS(' ', s.first_name, s.last_name) AS 'full_name'
FROM
student s
WHERE
LENGTH(CONCAT_WS(' ', s.first_name, s.last_name, NULL)) < 12;
Output
+----+------------+-----------+---------------+
| id | first_name | last_name | full_name |
+----+------------+-----------+---------------+
| 1 | Vishvajit | Rao | Vishvajit Rao |
+----+------------+-----------+---------------+
1 row in set (0.00 sec)Conclusion
So, In this article, we have seen everything about How to concatenate multiple columns in MySQL with the help of different-different examples. Both CONCAT() and CONCAT_WS() functions are very useful functions to add column values and these functions will save you more time. So from next, When your requirement is to contact two columns or more columns, without any issues you can go with these functions.
If you found this article helpful, please share this.
Related Articles:-
Thanks for your valuable time….
Have a nice day.
