CONCAT

Concatenates all of the values in its argument list.

If any of the arguments is NULL, the result is NULL. When PIPES_AS_CONCAT flag is on, the symbol || can be used as CONCAT as well.

Note

This function is not to be confused with GROUP_CONCAT, which is an aggregate function returning a concatenation of values passed in during aggregation.

Syntax

CONCAT(expression, [ expression, [expression ...]])
expression || expression || expression ... || expression

Arguments

  • expression: any set of expressions. This may be a column name, the result of another function, or a math operation.

Return Type

The concatenated string.

Remarks

  • The symbol || can take two different roles. If the sql_mode session variable is set as PIPES_AS_CONCAT, then || is an alias for the CONCAT function else, it is an alias for logical OR.

  • The value of the sql_mode variable is checked each time || symbol is encountered. The role of the symbol changes accordingly.

  • When used as concatenation operator, || symbol has a higher precedence than the logical OR.

Examples

SELECT CONCAT('1', '2', '3');
+-----------------------+
| CONCAT('1', '2', '3') |
+-----------------------+
| 123                   |
+-----------------------+
SELECT CONCAT('1', ' ', '2', ' ', '3');
+---------------------------------+
| CONCAT('1', ' ', '2', ' ', '3') |
+---------------------------------+
| 1 2 3                           |
+---------------------------------+
SELECT CONCAT(first_name, " ", last_name) FROM BFFs;
+------------------------------------+
| CONCAT(first_name, " ", last_name) |
+------------------------------------+
| Cecelia Cruz                       |
| Ari Floo                           |
| Skylar Rhodes                      |
+------------------------------------+

Using pipes (||) for concatenation:

SET @@sql_mode = PIPES_AS_CONCAT;
SELECT 'a' || 'b' || 'c';
+-------------------+
| 'a' || 'b' || 'c' |
+-------------------+
| abc               |
+-------------------+
SELECT first_name || " " || last_name FROM BFFs;
+--------------------------------+
| first_name || " " || last_name |
+--------------------------------+
| Cecelia Cruz                   |
| Ari Floo                       |
| Skylar Rhodes                  |
+--------------------------------+
SELECT '1 ' || CONCAT('2 ', '3');
+---------------------------+
| '1 ' || CONCAT('2 ', '3') |
+---------------------------+
| 1 2 3                     |
+---------------------------+

Last modified: June 15, 2023

Was this article helpful?