|| (Concatenation) operator - Amazon Redshift
SyntaxArgumentsReturn typeExamples
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

|| (Concatenation) operator

Concatenates two expressions on either side of the || symbol and returns the concatenated expression.

Similar to CONCAT function.

Note

If one or both of the expressions is null, the result of the concatenation is NULL.

Syntax

expression1 || expression2

Arguments

expression1

A CHAR string, a VARCHAR string, a binary expression, or an expression that evaluates to one of these types.

expression2

A CHAR string, a VARCHAR string, a binary expression, or an expression that evaluates to one of these types.

Return type

The return type of the string is the same as the type of the input arguments. For example, concatenating two strings of type VARCHAR returns a string of type VARCHAR.

Examples

The following examples use the USERS and VENUE tables from the TICKIT sample database. For more information, see Sample database.

To concatenate the FIRSTNAME and LASTNAME fields from the USERS table in the sample database, use the following example.

SELECT (firstname || ' ' || lastname) as fullname
FROM users
ORDER BY 1
LIMIT 10;

+-----------------+
|    fullname     |
+-----------------+
| Aaron Banks     |
| Aaron Booth     |
| Aaron Browning  |
| Aaron Burnett   |
| Aaron Casey     |
| Aaron Cash      |
| Aaron Castro    |
| Aaron Dickerson |
| Aaron Dixon     |
| Aaron Dotson    |
+-----------------+

To concatenate columns that might contain nulls, use the NVL and COALESCE functions expression. The following example uses NVL to return a 0 whenever NULL is encountered. 

SELECT (venuename || ' seats ' || NVL(venueseats, 0)) as seating
FROM venue
WHERE venuestate = 'NV' or venuestate = 'NC'
ORDER BY 1
LIMIT 10;

+-------------------------------------+
|               seating               |
+-------------------------------------+
| Ballys Hotel seats 0                |
| Bank of America Stadium seats 73298 |
| Bellagio Hotel seats 0              |
| Caesars Palace seats 0              |
| Harrahs Hotel seats 0               |
| Hilton Hotel seats 0                |
| Luxor Hotel seats 0                 |
| Mandalay Bay Hotel seats 0          |
| Mirage Hotel seats 0                |
| New York New York seats 0           |
+-------------------------------------+