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.