Categories:

String & Binary Functions (General)

CONCAT , ||

Concatenates one or more strings, or concatenates one or more binary values. If any of the values is null, the result is also null.

The || operator provides alternative syntax for CONCAT and requires at least two arguments.

See also:

CONCAT_WS

Syntax

CONCAT( <expr1> [ , <exprN> ... ] )

<expr1> || <expr2> [ || <exprN> ... ]

Arguments

exprN

The input expressions must all be strings, or all be binary values.

Returns

The data type of the returned value is the same as the data type of the input value(s).

Usage Notes

  • Metadata functions such as GET_DDL accept only constants as input. Concatenated input generates an error.

Collation Details

Examples

Concatenate two strings:

SELECT CONCAT('George Washington ', 'Carver');
+----------------------------------------+
| CONCAT('GEORGE WASHINGTON ', 'CARVER') |
|----------------------------------------|
| George Washington Carver               |
+----------------------------------------+

Concatenate two VARCHAR columns:

CREATE TABLE table1 (s1 VARCHAR, s2 VARCHAR, s3 VARCHAR);
INSERT INTO table1 (s1, s2, s3) VALUES 
    ('ye', 't', 'i'),
    ('Colorado ', 'River ', NULL);
SELECT CONCAT(s1, s2)
    FROM table1;
+-----------------+
| CONCAT(S1, S2)  |
|-----------------|
| yet             |
| Colorado River  |
+-----------------+

Concatenate more than two strings:

SELECT CONCAT(s1, s2, s3)
    FROM table1;
+--------------------+
| CONCAT(S1, S2, S3) |
|--------------------|
| yeti               |
| NULL               |
+--------------------+

Use the “||” concatenation operator instead of the function:

SELECT 'This ' || 'is ' || 'another ' || 'concatenation ' || 'technique.';
+--------------------------------------------------------------------+
| 'THIS ' || 'IS ' || 'ANOTHER ' || 'CONCATENATION ' || 'TECHNIQUE.' |
|--------------------------------------------------------------------|
| This is another concatenation technique.                           |
+--------------------------------------------------------------------+