MySQL - concatenate multiple columns into one
In this article, we would like to show you how to concatenate multiple columns into one in MySQL.
Quick solution:
xxxxxxxxxxSELECT CONCAT(`column1`, 'separator', `column2`, 'separator', `columnN`) AS 'alias_name'FROM `table_name`;xxxxxxxxxxSELECT CONCAT_WS('separator', `column1`, `column2`, `columnN`) AS 'alias_name'FROM `table_name`;To show how to combine multiple columns into one, we will use the following table:
Note:
At the end of this article you can find database preparation SQL queries.
In this example, we will concatenate name and surname columns into one - full_name using empty string (' ') as a separator. Then we will combine the rest of the columns into the second one - address using commas (', ') as separators.
Note:
Go to the next example to see how to specify the separator (such as
', ') only once when you want to concatenate many columns.
Query:
xxxxxxxxxxSELECT CONCAT(`name`, ' ', `surname`) AS 'full_name', CONCAT(`country`, ', ', `city`, ", ", `street`) AS 'address'FROM `users`;Output:
In this example, we will concatenate country, city and street columns into one - address, using CONCAT_WS() function.
Query:
xxxxxxxxxxSELECT `name`, `surname`, CONCAT_WS(', ', `country`, `city`, `street`) AS 'address'FROM `users`;Output:
create_tables.sql file:
xxxxxxxxxxCREATE TABLE `users` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `surname` VARCHAR(50) NOT NULL, `country` VARCHAR(100) NOT NULL, `city` VARCHAR(100) NOT NULL, `street` VARCHAR(100) NOT NULL, PRIMARY KEY (`id`));insert_data.sql file:
xxxxxxxxxxINSERT INTO `users` ( `name`, `surname`, `country`, `city`, `street`)VALUES ('Tomasz', 'Kowalski', 'Poland', 'Warsaw', 'Mickiewicza Street 1'), ('Chris', 'Brown', 'United States', 'New York', 'Main Street 12'), ('Minh', 'Nguyen', 'Vietnam', 'Hanoi', 'Dong Xuan Street 123');