Redshift - Merging two columns

Question

I have two columns that have customer name and product name. I would like to merge these two columns with a ' - ' between them.

Could anyone advice as to how to do this. I am using Redshift. I tried concat and string_agg but I am trying to see if there is an alternate way without the group by.

customer_name, product_name, expected_output
Kev, Prod_A, Kev - Prod_A

Answer 1

You may use the || concatenation operator as @Mureinik has mentioned. But, we can also use the CONCAT function here:

SELECT
    customer_name,
    product_name,
    CONCAT(CONCAT(customer_name, ' - '), product_name) AS output
FROM yourTable;

My guess as to why CONCAT wasn't working for you is that you were trying to pass more than 2 parameters to it. CONCAT in Redshift only takes two parameters, so we must chain them here to make it work.

Answer 2

You can use the || operator to concatenate strings:

SELECT customer_name, product_name, customer_name || ' - ' || product_name AS output
FROM   mytable