Redshift Concat Function

The Concat function in AWS Redshift is used to concatenate (join) two or more strings and returns the resulting string. The data type of the resulting variable is the same as that of the input variables to the Concat function. Concat function cannot be applicable on a single string. It can be applicable on two and more than two strings. In case we want to apply the Concat function on more than two strings, Nested Concat function is used. Concat function is equivalent to the Concatenation Operator(||) used between two strings or expressions.

Syntax of CONCAT function

The syntax for Concat function is given in the following:

Where both the arguments “string 1” and “string 2” can be of fixed length or of variable length strings.

If the input strings are of different data types, Amazon Redshift tries to unconditionally type casts one of the strings. If values can’t be casted, an error is returned from the Amazon Redshift.

Examples in Using the CONCAT Function

Let’s take a few examples to fully understand the working of the Concat function. This section contains multiple examples to have a better understanding of the CONCAT function.

• Simple String Concatenation
• Nested Concatenation
• Handling NULL values
• Applying CONCAT function on REDSHIFT table

Simple String Concatenation

In this example, we will concatenate the two strings – “AWS” and “Redshift”. Use the following Redshift query to concatenate these strings:

The output of this query is as follows:

The same result can also be generated by using the concatenation operator(||). Use the following query to concatenate the strings using the concatenation operator in the Redshift cluster:

The output of this query is as follows:

Now, let’s try to concatenate the strings, “21st May” and “2022”. The following Redshift query concatenates these strings using the CONCAT function:

It generates the following output. Remember that the data type of this query is the string as both the input parameters are strings:

Try the same example, but this time by using a concatenation operator(||). The following is the query to concatenate these strings:

This query results in the following output:

Nested Concatenation

If you want to concatenate more than two strings, the Nested Concat function is used. In the same way, the concatenation operator(||) can also be used to concatenate two or more than two strings in the Redshift cluster. In this section, we will use both the concatenation operator (||) and the CONCAT function to implement the Nested Concatenation.

In the following example, we will try to concatenate more than two strings to have a better understanding of the Concat function. The first string is a day, i.e. “Saturday”. The second string is a date and month, i.e. “21st May”. And the third string is a year, i.e. “2022”.

The following query is used to implement the nested concatenation on these strings:

This query generates the following output:

To concatenate multiple strings, we use the Concatenation Operator(||) instead of the Concat function in the Redshift cluster. The Concatenation Operator(||) takes the strings from both the sides and concatenates them to generate the output.

The following query concatenates multiple strings using the Concatenation Operator(||):

The output of this query is as follows:

Handling NULL Values in the Redshift

In case, if we pass one or more NULL values to the Concat function, the result will be empty. In order to check the empty value, we will run an example query on the Redshift cluster.

In the following query, one parameter of the Concat function is NULL:

When executed, this query will generate an empty output.

Now, we will try the same example using a Concatenation Operator(||) instead of the Concat function:

The output of this query will also be empty.

To solve the null value in the data, NVL command is used. NVL command is responsible for handling the NULL values in the data.

Now, we take an example in which we use the NVL to handle the NULL parameter. For that, we use the null handling function, i.e. NVL, to handle the null values.

The following query can be used to concatenate the string and the NULL parameter handled by the NVL function:

It generates the following output, taking the NULL parameter as an empty string:

Try the same example, but this time by using a concatenation operator:

The following is the output for this query:

Applying CONCAT Function on REDSHIFT Table

In this section, we will apply the CONCAT function in the table. We will use a database set by AWS to apply the CONCAT function on it.

We will apply the CONCAT function on the venue table provided by the Amazon REDSHIFT. We will concatenate both the venuename and venuecity columns where venueseats are greater than 75000.

The following query concatenates the data of venuename and venuecity where the venueseats are greater than 75000:

The output of this query is as follows:

Now, try this example using a concatenation operator. But, we concatenate the venuecity and veuestate columns this time. Also, we change the condition of venueseats to 73500:

It generates the following result:

Now, we apply the concept of nested CONCAT function in the venue table. The query concatenates the venuename and venuecity values from the table. Also, add the commas and spaces to the resulting string:

The output of this query is as follows:

In this following example, we will concatenate the column that contains the null to fully understand the concept of handling the NULL using the NVL function:

This query generates the following result when executed:

Conclusion

In this article, we discussed how to use the CONCAT function with the help of multiple examples. We applied the concept of the Concat function, Nested Concat function, and Concatenation Operator. We studied how to handle the NULL values in the CONCAT function using the NVL function. The AWS Redshift Concat function provides the full privilege to concatenate the two or more strings into your database.