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.

Syntax

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

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

Arguments

exprN

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

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.                           |
+--------------------------------------------------------------------+