String concatenation with MySQL

MySQL has a number of string handling functions including functions to concatenate text or database columns into a single column in the resultset. I'm always forgetting that the function is CONCAT and not CONCATENATE so maybe by writing about it I will actually remember...

Example data

The example table used in this post is called "products" and contains the following data:

+------------+------+--------------------+--------+
| product_id | name | description        | price  |
+------------+------+--------------------+--------+
|          1 | Foo  | Blah blah foo blah |  55.00 |
|          2 | Bar  | Blah blah bar blah | 102.00 |
|          3 | Baz  | Blah blah baz blah |  87.50 |
|          4 | Bat  | Blah blah bat blah |  42.00 |
+------------+------+--------------------+--------+

Using concat to concatenate fields and strings

To return a resultset which contains the name and description columns as a single string column where the name and description values are separated by a dash, you could do this:

SELECT CONCAT(name, ' - ', description) FROM products;

which would give us a resultset like this:

+----------------------------------+
| CONCAT(name, ' - ', description) |
+----------------------------------+
| Foo - Blah blah foo blah         |
| Bar - Blah blah bar blah         |
| Baz - Blah blah baz blah         |
| Bat - Blah blah bat blah         |
+----------------------------------+

Note that the column name is "CONCAT(name, ' - ', description)" so you'd either want to access the column using a numeric index or use "AS" syntax to give it a more user friendly name:

 SELECT CONCAT(name, ' - ', description) AS name_description FROM products;
+--------------------------+
| name_description         |
+--------------------------+
| Foo - Blah blah foo blah |
| Bar - Blah blah bar blah |
| Baz - Blah blah baz blah |
| Bat - Blah blah bat blah |
+--------------------------+

And finally, don't make my mistake and think the function is called CONCATENATE otherwise you'll get this error:

ERROR 1305 (42000): FUNCTION test.CONCATENATE does not exist

So that's how to use the CONCAT function in MySQL to concatenate strings. While this probably isn't a very realistic example to have used it does illustrate how to use the function and I've had to use it a number of times in my own projects.

CONCAT_WS

A few months ago I posted about string concatenation with MySQL using the CONCAT function and in this follow up post look at the CONCAT_WS function which glues the fields together with the specified string. CONCAT_WS means CONCATenate With Separator i.e. join the fields together with a specified separator string.

Example data

The example table used in this post is called "products" and contains the following data, which is the same as in the original post:

+------------+------+--------------------+--------+
| product_id | name | description        | price  |
+------------+------+--------------------+--------+
|          1 | Foo  | Blah blah foo blah |  55.00 |
|          2 | Bar  | Blah blah bar blah | 102.00 |
|          3 | Baz  | Blah blah baz blah |  87.50 |
|          4 | Bat  | Blah blah bat blah |  42.00 |
+------------+------+--------------------+--------+

Using concat_ws to concatenate fields and strings

CONCAT_WS takes multiple arguments, the first being the string that glues the rest of the fields together. In my original post I joined the strings together with a hyphen surrounded by spaces with CONCAT ("SELECT CONCAT(name, ' - ', description) FROM products;") and this can be done instead with CONCAT_WS like so:

SELECT CONCAT_WS(' - ', name, description) FROM products;

The resulting data would look like this:

+-------------------------------------+
| CONCAT_WS(' - ', name, description) |
+-------------------------------------+
| Foo - Blah blah foo blah            |
| Bar - Blah blah bar blah            |
| Baz - Blah blah baz blah            |
| Bat - Blah blah bat blah            |
+-------------------------------------+

This can be a lot simpler to join multiple fields than using CONCAT if you are wanting to concatenate more than two fields.

Related posts:

• Rounding numbers with MySQL
• Maximum length for MySQL TEXT field types
• Using SELECT REPLACE with MySQL
• Upper case and lower case strings with MySQL
• Find the length of the longest string in MySQL
• Formatting Dates and Times with MySQL