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
Be sure to read the follow up post to this one which looks at the CONCAT_WS function which uses a glue string to join the fields together.
