Table of Contents for

MySQL in a Nutshell, 2nd Edition

With this function, strings or columns can be concatenated or pasted together into one resulting field. Any number of strings may be specified, with each argument separated by a comma. If any of the values given are NULL, a NULL value is returned. Here is an example:

SELECT CONCAT(name_first, ' ', name_last) AS Student
FROM students WHERE name_last = 'Dyer';

+-------------------+
| Student           |
+-------------------+
| Kenneth Dyer      |
| Geoffrey Dyer     |
| Marie Dyer        |
| NULL              |
+-------------------+

In this example, the database contained four students with the last name Dyer, but one of them had a NULL value in the name_first column. Within the parentheses of the function, notice that a space is given within quotes as the second element so that the results show a space between each student’s first and last name.

Another use for CONCAT() is to convert numeric values of a given column to strings. This may be useful when working with an API such as Perl and when using UNION to mix data from two different data types.

Here is an example:

SELECT CONCAT(type_id) AS id, type AS title
  FROM types
UNION
SELECT topic_id AS id, topic AS title
  FROM topics;

In this example, the column type_id is an INT, whereas the column topic_id is a CHAR column. In MySQL, the results can be mixed. However, if this SQL statement is used to create a hash of data in Perl or another API language, you may encounter problems retrieving data. In order that the data in the columns agree, the CONCAT() function is used to convert the numeric values to their string equivalents.