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

Concatenate strings and arrays

Concatenating strings and concatenating arrays use similar techniques.

Concatenate strings

To concatenate two strings, you can use the double pipe || operator, as in the following example.

SELECT 'This' || ' is' || ' a' || ' test.' AS Concatenated_String

This query returns:

You can use the concat() function to achieve the same result.

SELECT concat('This', ' is', ' a', ' test.') AS Concatenated_String

This query returns:

You can use the concat_ws() function to concatenate strings with the separator specified in the first argument.

SELECT concat_ws(' ', 'This', 'is', 'a', 'test.') as Concatenated_String

This query returns:

To concatenate two columns of the string data type using a dot, reference the two columns using double quotes, and enclose the dot in single quotes as a hard-coded string. If a column is not of the string data type, you can use CAST("column_name" as VARCHAR) to cast the column first.

SELECT "col1" || '.' || "col2" as Concatenated_String
FROM my_table

This query returns:

Concatenate arrays

You can use the same techniques to concatenate arrays.

To concatenate multiple arrays, use the double pipe || operator.

SELECT ARRAY [4,5] || ARRAY[ ARRAY[1,2], ARRAY[3,4] ] AS items

This query returns:

To combine multiple arrays into a single array, use the double pipe operator or the concat() function.

WITH
dataset AS (
  SELECT
    ARRAY ['Hello', 'Amazon', 'Athena'] AS words,
    ARRAY ['Hi', 'Alexa'] AS alexa
)
SELECT concat(words, alexa) AS welcome_msg
FROM dataset

This query returns:

For more information about concat() other string functions, see String functions and operators in the Trino documentation.