Categories:

String & binary functions (General)

CONCAT , ||¶

Concatenates one or more strings, or concatenates one or more binary values.

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

See also:

CONCAT_WS

Syntax¶

CONCAT( <expr> [ , <expr> ... ] )

<expr> || <expr> [ || <expr> ... ]
Copy

Arguments¶

expr

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

If any input value is NULL, returns NULL.

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');
Copy
+----------------------------------------+
| CONCAT('GEORGE WASHINGTON ', 'CARVER') |
|----------------------------------------|
| George Washington Carver               |
+----------------------------------------+

Concatenate two VARCHAR columns. First, create a table and insert data:

CREATE OR REPLACE TABLE concat_function_example (s1 VARCHAR, s2 VARCHAR, s3 VARCHAR);
INSERT INTO concat_function_example (s1, s2, s3) VALUES
  ('co', 'd', 'e'),
  ('Colorado ', 'River ', NULL);
Copy

Run a query:

SELECT CONCAT(s1, s2)
  FROM concat_function_example;
Copy
+-----------------+
| CONCAT(S1, S2)  |
|-----------------|
| cod             |
| Colorado River  |
+-----------------+

Concatenate more than two strings:

SELECT CONCAT(s1, s2, s3)
  FROM concat_function_example;
Copy
+--------------------+
| CONCAT(S1, S2, S3) |
|--------------------|
| code               |
| NULL               |
+--------------------+

Use the IFF function with the CONCAT function to concatenate strings that are not NULL:

SELECT CONCAT(
    IFF(s1 IS NULL, '', s1),
    IFF(s2 IS NULL, '', s2),
    IFF(s3 IS NULL, '', s3)) AS concat_non_null_strings
  FROM concat_function_example;
Copy
+-------------------------+
| CONCAT_NON_NULL_STRINGS |
|-------------------------|
| code                    |
| Colorado River          |
+-------------------------+

Use the || concatenation operator instead of the function:

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