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:

SELECT CONCAT(`column1`, 'separator', `column2`, 'separator', `columnN`) AS 'alias_name'
FROM `table_name`;

SELECT CONCAT_WS('separator', `column1`, `column2`, `columnN`) AS 'alias_name'
FROM `table_name`;

Practical example

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.

Example 1 - using CONCAT()

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:

SELECT 
    CONCAT(`name`, ' ', `surname`) AS 'full_name',
    CONCAT(`country`, ', ', `city`, ", ", `street`) AS 'address'
FROM `users`;

Output:

Example 2 - using CONCAT_WS()

In this example, we will concatenate country, city and street columns into one - address, using CONCAT_WS() function.

Query:

SELECT
    `name`, `surname`, 
    CONCAT_WS(', ', `country`, `city`, `street`) AS 'address'
FROM `users`;

Output:

Database preparation

create_tables.sql file:

CREATE 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:

INSERT 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');