How to Concatenate Multiple columns in MySQL

In this tutorial, I show How you can concatenate multiple columns in MySQL.

You can simply do this programmatically by separately select fields from MySQL Table and store their values in the single variable after concat their values.

But you can make the above process little simpler by concatenating the values while selecting rows from DataBase Table.

Let’s take a simple example –

You have two columns – firstname, lastname within your DataBase Table you want to show both the columns values in a single string form. In case you can MySQL functions to combine the values of the columns.

There are two functions for doing this –

• CONCAT
• CONCAT_WS

Both functions work similar but have little difference.

---

Contents

1. CONCAT
2. CONCAT_WS
3. Using them in WHERE CLAUSE
4. Conclusion

---

1. CONCAT

This function is used to concatenate multiple columns or strings into a single one. Arguments are separated by a comma.

Syntax – 

CONCAT( column1, column2, ... )

OR

CONCAT ( string1, string2, ... )

For demonstration, I am using Users Table which has following records.

id	username	firstname	lastname
1	yssyogesh	Yogesh	Singh
2	sonarika	Sonarika	Bhadoria
3	vishal	Vishal	Sahu

Example

I am using this function to concatenate firstname, lastname columns and set it ALIAS to fullname.

SELECT 
username, 
CONCAT( firstname, " ", lastname ) AS fullname 
FROM users

Output

id	username	fullname
1	yssyogesh	Yogesh Singh
2	sonarika	Sonarika Bhadoria
3	vishal	Vishal Sahu

---

2. CONCAT_WS

The CONCAT_WS() function not only add multiple string values and makes them a single string value. It also let you define separator ( ” “, ” ,  “, ” – “,” _ “, etc.).

Syntax – 

CONCAT_WS( SEPERATOR, column1, column2, ... )

OR

CONCAT ( SEPERATOR, string1, string2, ... )

Example 

SELECT 
username, 
CONCAT_WS( " ", firstname, lastname ) AS fullname 
FROM users

Output

id	username	fullname
1	yssyogesh	Yogesh Singh
2	sonarika	Sonarika Bhadoria
3	vishal	Vishal Sahu

As I said at the start of the function you can define any other characters instead of space.

---

3. Using them in WHERE CLAUSE

You can use both of them in WHERE CLAUSE for selection based on condition.

Example

SELECT * 
FROM users 
WHERE CONCAT_WS(" ",firstname,lastname) = "Sonarika Bhadoria"

Output

id	username	firstname	lastname
2	sonarika	Sonarika	Bhadoria

Same you can do with CONCAT function.

---

4. Conclusion

This functions generally you can use when you have to show multiple columns values within the single string. You can specify your own separator values like – space, comma, dash, etc in the function.

This saves you some of the time on the programming side where you add the column values separately.

How to Add a Separator to a Concatenated String in MySQL – CONCAT_WS()

In MySQL, the CONCAT_WS() function allows you to add a separator to concatenated strings. If you just use the CONCAT() function, you’d have no separator (unless you explicitly added a separator as an argument between each string argument).

A common usage of the CONCAT_WS() function is to create a comma-delimited list.

Here’s an example:

SELECT CONCAT_WS(',','Sydney', 'Australia') AS Location;

Result:

+------------------+
| Location         |
+------------------+
| Sydney,Australia |
+------------------+

And you can add a space in there if you want:

SELECT CONCAT_WS(', ','Sydney', 'Australia') AS Location;

Result:

+-------------------+
| Location          |
+-------------------+
| Sydney, Australia |
+-------------------+

The Separator

There’s nothing to say that the separator must be a comma. The separator can be any string.

Here’s the same example as the previous one, except this one uses a different separator.

SELECT CONCAT_WS(' - ','Paris', 'France') AS Location;

Result:

+----------------+
| Location       |
+----------------+
| Paris - France |
+----------------+

A Database Example

Here’s an example of retrieving data from a database, and combining two columns into one, separated by a comma:

SELECT CONCAT_WS(', ', city.Name, country.Name ) AS Location
FROM city
INNER JOIN country ON
city.CountryCode = country.Code
WHERE country.Code = 'NZL';

Result:

+---------------------------+
| Location                  |
+---------------------------+
| Auckland, New Zealand     |
| Christchurch, New Zealand |
| Manukau, New Zealand      |
| North Shore, New Zealand  |
| Waitakere, New Zealand    |
| Wellington, New Zealand   |
| Dunedin, New Zealand      |
| Hamilton, New Zealand     |
| Lower Hutt, New Zealand   |
+---------------------------+

NULL Values

If any of the arguments is a NULL value, MySQL will skip that value and its separator, but it will still process the others.

Example:

SELECT CONCAT_WS(', ','Auckland', NULL, 'New Zealand') AS Location;

Result:

+-----------------------+
| Location              |
+-----------------------+
| Auckland, New Zealand |
+-----------------------+

NULL Value Separator

If the separator itself is a NULL value, the concatenation operation will return NULL.

Example:

SELECT CONCAT_WS(NULL,'Auckland', NULL, 'New Zealand') AS Location;

Result:

+----------+
| Location |
+----------+
| NULL     |
+----------+

This is one of the differences between MySQL and T-SQL (SQL Server, Azure). In T-SQL, if the separator is a NULL value, the the string values are still concatenated, but without a separator.