How to Concatenate Strings in MySQL

Hi, In this you are going to learn everything about how to concatenate strings in MySQL. Throughout we are about to explore various ways to concatenate strings in MySQL so that you use any one of them at your convenience.

Basically, Strings concatenation is a mechanism of adding two or more strings together by a separator not. To concatenate strings in MySQL, Mysql offers two built-in functions which are CONCAT and CONCAT_WS, here by saying builT-in functions means it comes with MySQL server by default.

Strings concatenation in MySQL is a very clean and 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 concatenated versions of strings.

let’s understand both functions along with examples.

MySQL CONCAT() Function

The CONCAT() function in MySQL is a built-in function that comes with MySQL by default it takes various arguments as parameters and returns concatenated version of all those strings. The CONCAT() function must require at least one argument otherwise it will raise an error.

The CONCAT() function first converts all the passed arguments into string type and then concatenate strings in MySQL. 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 strings in MySQL together.

Example: Concatenate hard-coded strings together

mysql> select CONCAT('Programming', ' ', 'Funda') as 'concatenated_string';
+---------------------+
| concatenated_string |
+---------------------+
| Programming Funda   |
+---------------------+
1 row in set (0.00 sec)

As you can see in the above example, how we have added two strings together. The middle empty string work as a separator between the first and last string.

Example: Concatenate null value with a string

In this example, I am trying to contact the Null value with strings. I am just taking the above example.

mysql> select CONCAT('Programming', ' ', 'Funda', ' ', Null) as 'concatenated_string';
+---------------------+
| concatenated_string |
+---------------------+
| NULL                |
+---------------------+
1 row in set (0.00 sec)

As you can see in the above output, the result is NULL because when we pass any Null value inside CONCAT() function it will return NULL.

So this is how we can use MySQL CONCAT() function to concatenate one or more strings together.

MySQL CONCAT_WS() Function

The CONCAT_WS() is an important function in MySQL to add or concatenate two or more strings 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 a concatenated string 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 hard-coded string using concat_ws() function

mysql> select concat_ws(' ', 'Programming', 'Funda') as 'Website';
+-------------------+
| Website           |
+-------------------+
| Programming Funda |
+-------------------+
1 row in set (0.00 sec)

As you can see in the above example, How we have added Programming and Funda together with separator spaces ( ‘ ‘ ). The first parameter of the CONCAT_WS() function always should be a separator, you can give any separator there.

Let’s use double stars (**) as a separator.

mysql> select concat_ws('**', 'Programming', 'Funda') as 'Website';
+--------------------+
| Website            |
+--------------------+
| Programming**Funda |
+--------------------+
1 row in set (0.00 sec)

As you can see here, how we have concatenated two words together with separator double stars ( ** ).

Example: Concatenate Null value with strings using concat_ws()

In this article, I am about to add two strings with Null values. Let’s see what happens.

mysql> select concat_ws(' ', 'Programming', 'Funda', Null, 'is' ,'Best') as 'Website';
+---------------------------+
| Website                   |
+---------------------------+
| Programming Funda is Best |
+---------------------------+
1 row in set (0.00 sec)

As we can see in the above example, When we try to concatenate a Null value with strings, It neglects the Null value and concatenates only valid strings.

So, this is how we can use CONCAT() and CONCAT_WS() functions to concatenate strings in MySQL.

How to concatenate column values in MySQL?

Apart from adding hard-coded strings, in a real-life project, most of the time your will be concatenating columns in MySQL.

I have a table employee having some records, as you can see below. Now I am going to concatenate two columns in MySQL which are first_name and last_name.

To concatenate multiple columns in MySQL, CONCAT() and CONCAT_WS() will be used which we have seen earlier in the above examples.

let’s use these both functions to add values to two columns.

employee table records:

mysql> select * from employee;
+----+------------+-----------+--------+------+-----------+
| id | first_name | last_name | gender | age  | salary    |
+----+------------+-----------+--------+------+-----------+
|  1 | Vishvajit  | Rao       | Male   |   24 |  25000.00 |
|  2 | Vinay      | Kumar     | Male   |   22 |  35000.00 |
|  3 | Minakshi   | Kumary    | Female |   25 |  30000.00 |
|  4 | John       | Doe       | Male   |   30 |  80000.00 |
|  5 | Akash      | Saini     | Male   |   23 |  25500.00 |
|  6 | Harshita   | Singh     | Female |   24 |  20000.00 |
|  7 | Manish     | Das       | Male   |   35 |  90000.00 |
|  8 | David      | Russel    | Male   |   30 | 105000.00 |
|  9 | Nishant    | Chahal    | Male   |   30 |  65000.00 |
+----+------------+-----------+--------+------+-----------+
16 rows in set (0.00 sec)

Example:- concatenate two columns in MySQL with space using the CONCAT() function

mysql> select *, concat(e.first_name, ' ', e.last_name) as 'full_name' from employee e;
+----+------------+-----------+--------+------+-----------+------------------+
| id | first_name | last_name | gender | age  | salary    | full_name        |
+----+------------+-----------+--------+------+-----------+------------------+
|  1 | Vishvajit  | Rao       | Male   |   24 |  25000.00 | Vishvajit Rao    |
|  2 | Vinay      | Kumar     | Male   |   22 |  35000.00 | Vinay Kumar      |
|  3 | Minakshi   | Kumary    | Female |   25 |  30000.00 | Minakshi Kumary  |
|  4 | John       | Doe       | Male   |   30 |  80000.00 | John Doe         |
|  5 | Akash      | Saini     | Male   |   23 |  25500.00 | Akash Saini      |
|  6 | Harshita   | Singh     | Female |   24 |  20000.00 | Harshita Singh   |
|  7 | Manish     | Das       | Male   |   35 |  90000.00 | Manish Das       |
|  8 | David      | Russel    | Male   |   30 | 105000.00 | David Russel     |
|  9 | Nishant    | Chahal    | Male   |   30 |  65000.00 | Nishant Chahal   |
+----+------------+-----------+--------+------+-----------+------------------+

So, In this example, we have successfully concatenated two columns in a MySQL table called employee.

Example: concatenate two columns in MySQL with space using the concat_ws() function

mysql> select *, concat_ws(' ', e.first_name, e.last_name) as 'full_name' from employee e;
+----+------------+-----------+--------+------+-----------+------------------+
| id | first_name | last_name | gender | age  | salary    | full_name        |
+----+------------+-----------+--------+------+-----------+------------------+
|  1 | Vishvajit  | Rao       | Male   |   24 |  25000.00 | Vishvajit Rao    |
|  2 | Vinay      | Kumar     | Male   |   22 |  35000.00 | Vinay Kumar      |
|  3 | Minakshi   | Kumary    | Female |   25 |  30000.00 | Minakshi Kumary  |
|  4 | John       | Doe       | Male   |   30 |  80000.00 | John Doe         |
|  5 | Akash      | Saini     | Male   |   23 |  25500.00 | Akash Saini      |
|  6 | Harshita   | Singh     | Female |   24 |  20000.00 | Harshita Singh   |
|  7 | Manish     | Das       | Male   |   35 |  90000.00 | Manish Das       |
|  8 | David      | Russel    | Male   |   30 | 105000.00 | David Russel     |
|  9 | Nishant    | Chahal    | Male   |   30 |  65000.00 | Nishant Chahal   |
+----+------------+-----------+--------+------+-----------+------------------+

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 ‘Vinay Kumar‘.

Example: Using MySQL concat_ws() function with where clause

mysql> select *, concat_ws(' ', e.first_name, e.last_name) as 'full_name' from employee e where concat_ws(' ', e.first_name, e.last_name) = 'Vinay Kumar';
+----+------------+-----------+-------------+
| id | first_name | last_name | full_name   |
+----+------------+-----------+-------------+
|  2 | Vinay      | Kumar     | Vinay Kumar |
+----+------------+-----------+-------------+
1 row in set (0.19 sec)

Example: Using MySQL concat() function with where clause

mysql> select *, concat(e.first_name, ' ', e.last_name) as 'full_name' from employee e where concat(e.first_name, ' ', e.last_name) = 'Vinay Kumar';
+----+------------+-----------+---------------+
| id | first_name | last_name | full_name     |
+----+------------+-----------+---------------+
|  2 | Vinay      | Kumar     | Vinay Kumar |
+----+------------+-----------+---------------+
1 row in set (0.00 sec)

Note:- In all the above examples, we have concatenated only two string and two columns but you can provide more strings and columns as per your requirement.

👉 Check length of string and columns values:- Click Here

Summary

So, In this article, we have seen everything about How to concatenate strings in MySQL with the help of different-different examples. Both CONCAT() and CONCAT_WS() functions are very useful functions to add string values and these functions will save you more time.
So next, When you require it to concatenate two strings or column values, without any issues you can go with these functions.

If you found this article helpful, please share this.

CONCAT() Function Ref:- Click Here

CONCAT_WS() Function Ref:- Click Here

Thanks for your valuable time…. ❤️❤️🙏🙏

Have a nice day.