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.
Table of Content
- MySQL CONCAT() function
- MySQL CONCAT_WS() function
- Using CONCAT() AND CONCAT_WS() function in WHERE CLAUSE
- 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.