With this MySQL function, strings or columns can be concatenated or pasted together into one resulting field.
Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).
CONCAT(string, . . . )
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.