How to Concatenate Strings in MySQL with CONCAT()

MySQL has the CONCAT() function, which allows you to concatenate two or more strings. The function actually allows for one or more arguments, but its main use is to concatenate two or more strings.

In MySQL (and in any computer programming environment), string concatenation is the operation of joining character strings end-to-end.

Here’s an example:

SELECT CONCAT('Homer', ' ', 'Simpson') AS 'Full Name';

Result:

+---------------+
| Full Name     |
+---------------+
| Homer Simpson |
+---------------+

Note that I actually concatenated 3 strings here. I concatenated the first name, the last name, plus a space.

If I didn’t add the space it would’ve looked like this:

SELECT CONCAT('Homer', 'Simpson') AS 'Full Name';

Result:

+--------------+
| Full Name    |
+--------------+
| HomerSimpson |
+--------------+

Which may or may not be the result you’re looking for.

So if we apply this to a database, then the query might look something like this:

SELECT CONCAT(FirstName, ' ', LastName) AS 'Full Name'
FROM Individuals
WHERE IndividualId = '1';

Result:

+---------------+
| Full Name     |
+---------------+
| Homer Simpson |
+---------------+

If you’re concatenating more than two strings, and you need a space (or other separator), consider using the CONCAT_WS() function. This allows you to specify a separator to be used in between each string. You only need to specify the separator once, and it’s used on every string that’s concatenated, therefore saving you from having to re-type it between each string.

NULL Arguments

The CONCAT() function returns NULL if any argument is NULL.

Example:

SELECT CONCAT('Homer', NULL, 'Simpson') AS 'Full Name';

Result:

+-----------+
| Full Name |
+-----------+
| NULL      |
+-----------+

Binary Strings vs Nonbinary Strings

The MySQL documentation states:

If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form.

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