MySQueaL Resources

CONCAT( ) Function

With this MySQL function, strings or columns can be concatenated or pasted together into one resulting field.

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.

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

+-------------------+
| Student           |
+-------------------+
| Luca Caporale     |
| Marco Caporale    |
| Paola Caporale    |
| Simone Caporale   |
| NULL              |
+-------------------+

In this example, the database contained four students with the last name Caporale, but for one of them there was 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 like Perl and when using the clause UNION to mix data from two different data types. Below is an example of this:

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. For 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 in retrieving data properly. So that the data in the columns agree, the CONCAT( ) function is used to convert the numeric values to their string equivalents.