Join the SingleStore Community Today
Get expert advice, develop skills, and connect with others.

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.

Info

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

memsql> select concat('1', '2', '3');

+-----------------------+
| concat('1', '2', '3') |
+-----------------------+
| 123                   |
+-----------------------+

memsql> select concat('1', ' ', '2', ' ', '3');

+---------------------------------+
| concat('1', ' ', '2', ' ', '3') |
+---------------------------------+
| 1 2 3                           |
+---------------------------------+

memsql> select concat(first_name, " ", last_name) from BFFs;

+------------------------------------+
| concat(first_name, " ", last_name) |
+------------------------------------+
| Cecelia Cruz                       |
| Ari Floo                           |
| Skylar Rhodes                      |
+------------------------------------+

memsql> set @@sql_mode = PIPES_AS_CONCAT;

memsql> select 'a' || 'b' || 'c';

+-------------------+
| 'a' || 'b' || 'c' |
+-------------------+
| abc               |
+-------------------+

memsql> select first_name || " " || last_name from BFFs;

+--------------------------------+
| first_name || " " || last_name |
+--------------------------------+
| Cecelia Cruz                   |
| Ari Floo                       |
| Skylar Rhodes                  |
+--------------------------------+

memsql> select '1 ' || concat('2 ', '3');
+---------------------------+
| '1 ' || concat('2 ', '3') |
+---------------------------+
| 1 2 3                     |
+---------------------------+

Related Topics