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 thesql_mode
session variable is set asPIPES_AS_CONCAT
, then||
is an alias for the CONCAT function else, it is an alias for logicalOR
.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 logicalOR
.
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 | +---------------------------+