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 be all strings, or all binary values.

Returns

The data type of the returned value is the same as the data type of the input values.

If any input value is NULL, the function 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 five strings, using session variables for three of them:

SET var_first_name = 'George';
SET var_middle_name = 'Washington';
SET var_last_name = 'Carver';

SELECT CONCAT($var_first_name, ' ', $var_middle_name, ' ', $var_last_name) AS concat_name;
Copy
+--------------------------+
| CONCAT_NAME              |
|--------------------------|
| 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