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 a 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 this case, you can use MySQL functions to combine the values of the columns.

There are two functions for doing this –

  • CONCAT
  • CONCAT_WS

Both functions work similarly but have little difference.

How to Concatenate Multiple columns in MySQL


Table of Content

  1. MySQL CONCAT() function
  2. MySQL CONCAT_WS() function
  3. Using CONCAT() AND CONCAT_WS() function in WHERE CLAUSE
  4. Conclusion

1. MySQL CONCAT() function

This function is used to concat 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 the following records.

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


Example

I am using concat() function to concat two columns – firstname, lastname 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. MySQL CONCAT_WS() function

The CONCAT_WS() function not only adds 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 CONCAT() AND CONCAT_WS() function 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

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

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

If you found this tutorial helpful then don't forget to share.