Amazon Redshift CONCAT Function-Syntax and Examples

The data warehouse is built from many heterogeneous data sources. It is a common requirement to combine data from multiple columns or two strings before loading them to target table. For example, you may get requirement to combine state and city columns before loading data to the customer table. In this article, we will check Amazon AWS Redshift CONCAT function, its Syntax and examples. We will also check how to combine two or more columns using Redshift CONCAT operator (||).

Amazon Redshift provides multiple methods to concatenate the strings

The CONCAT Function
The concatenation operator (||)

AWS Redshift CONCAT Function

The CONCAT function in Redshift concatenates two character strings and returns the resulting string. Note that, CONCAT function works on only two character strings.

Redshift CONCAT Function Syntax

Following is the CONCAT function syntax

CONCAT ( string1, string2 )

Where, both arguments can be fixed-length or variable-length character strings or expressions.

Redshift CONCAT Simple Examples

Following example concatenates two strings.

select concat('Bangalore, ', 'Karnataka');
        concat
----------------------
 Bangalore, Karnataka
(1 row)

Redshift Nested CONCAT Functions

You can use nested CONCAT functions if your requirement is to concatenate more than two strings. You can also use the concatenation operator in place of nested concat functions if your requirement is to concatenate more than two strings.

Following is the nested CONCAT functions example

select CONCAT('Nested', CONCAT(' CONCAT', ' example!'));
         concat
------------------------
 Nested CONCAT example!
(1 row)

Redshift Concatenation Operator (||)

The concatenation operator concatenates two strings on either side of the || symbol and returns the concatenated string. This method is preferable when your requirement is to concatenate more than two strings.

For example,

select 'Nested' || ' CONCAT' || ' example!' as sample;
         sample
------------------------
 Nested CONCAT example!
(1 row)

Handling NULL Values in CONCAT function and the Concatenation operator

For both the CONCAT function and the concatenation operator, if one or both strings are null, the result of the concatenation is null.

For example,

select concat('Bangalore, ', NULL);
 concat
--------

 NULL
(1 row)

select 'Bangalore, ' || NULL as sample;
 sample
--------

 NULL

(1 row)

Therefore, it is always better to use Redshift NULL handling functions to deal with null values in your data.

For example,

select concat('Bangalore, ', NVL(NULL, ''));
   concat
-------------
 Bangalore,
(1 row)

select 'Bangalore, ' || NVL(NULL, '') as sample;
   sample
-------------
 Bangalore,
(1 row)