Often web developers and database administrators need to combine two or more columns in MySQL. While this is easy to do, many users end up getting syntax errors, since there are so many different ways to concatenate columns and strings, across different database systems and programming languages. In this article, we will learn how to combine columns in MySQL easily.
How to Combine Columns in MySQL
Let us say you have the following MySQL table.
mysql> create table sales(id int, created_at datetime, amount int);
mysql> insert into sales(id, created_at, amount)
values(1, '2022-11-15 03:00:00', 180),
(2, '2022-11-16 05:30:00', 100),
(3, '2022-11-17 04:15:00', 280),
(4, '2022-11-18 02:40:00', 150);
mysql> select * from sales;
+------+---------------------+--------+
| id | created_at | amount |
+------+---------------------+--------+
| 1 | 2022-11-15 03:00:00 | 180 |
| 2 | 2022-11-16 05:30:00 | 100 |
| 3 | 2022-11-17 04:15:00 | 280 |
| 4 | 2022-11-18 02:40:00 | 150 |
+------+---------------------+--------+
Let us say you want to combine columns id and created_at. Typically, users try concatenation using || or + operators, both of which do not provide desired result. They neither give an error which only makes things worse, in case you have used this query somewhere deep in your code.
The simplest and most foolproof way to concatenate columns in MySQL is to use concat() function. Here is its syntax.
concat(column1, column2, ...)
Here is the query to combine columns id and created_at using concat() function.
mysql> select concat(id, created_at) from sales; +------------------------+ | concat(id, created_at) | +------------------------+ | 12022-11-15 | | 22022-11-16 | | 32022-11-17 | | 42022-11-18 | +------------------------+
In this case, we have combined an int and date column but the result is a string since both these are different data types. If we were combine two or more int columns, the result would have been an int data type.
You can also use this function in UPDATE statement in case you want to update the underlying column value. Here is an example.
mysql> update sales set amount=concat(amount,id); mysql> select * from sales; +------+------------+--------+ | id | created_at | amount | +------+------------+--------+ | 1 | 2022-11-15 | 1801 | | 2 | 2022-11-16 | 1002 | | 3 | 2022-11-17 | 2803 | | 4 | 2022-11-18 | 1504 | +------+------------+--------+
However, while using concat() function in UPDATE statement, please note that the data types of columns being combined should be compatible with the column where the result is being stored. Otherwise it will give an error. Here is an example where we concatenate amount and created_at columns and store its value in amount column. Since amount is int and created_at is a date, this gives an error.
mysql> show columns from sales; +------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | created_at | date | YES | | NULL | | | amount | int(11) | YES | | NULL | | +------------+---------+------+-----+---------+-------+ mysql> update sales set amount=concat(amount,created_at); ERROR 1265 (01000): Data truncated for column 'amount' at row 1
On the other hand, such combinations are allowed if you simply do it in select statement as shown below. So you can use this approach as a workaround perhaps.
mysql> select concat(amount,created_at) from sales; +---------------------------+ | concat(amount,created_at) | +---------------------------+ | 18012022-11-15 | | 10022022-11-16 | | 28032022-11-17 | | 15042022-11-18 | +---------------------------+
In this article, we have learnt how to combine two or more columns in MySQL.
Also read:
How to Get List of Stored Procedure & Functions in MySQL
How to Select Rows Where Date Matches Day in MySQL
How to Copy Row and Insert to Same Table in MySQL
How to Run SQL Script in MySQL
How to Find Number of Columns in MySQL Table