Concat more than two columns or values

Adrian_1

Hello,

in the SQL documentation you write that the concat-statement is able to concatenate two OR MORE columns. Unfortunately, it does not work on "my" Vertica.

The following statement creates an error:
select concat('a','b','c') from a_test;

Error message: SQL Error [3457] [42883]: [Vertica][VJDBC](3457) ERROR: Function concat(unknown, unknown, unknown) does not exist, or permission is denied for concat(unknown, unknown, unknown)

This works fine:
select concat('a','b') from a_test;

(The test of the concatenation of different columns created to same errors)

Using Vertica 7. I'm able to nest the concats but if I want to nest ~200 columns, this will be very, very complicated.

Additional question: Is there anything equal to concat_ws() for vertica?

Thank you for your reply!

Siddarth

You can concatenate columns as shown below instead of using concat function.

SELECT a||b||c from test;

Adrian_1

Works great, thanks!

Navin_C

Hi Adrian,

Siddharth gave the best and easiest approach to concat the columns

one more alternative for this use case
1. Using nested concats

nnani=> select concat(concat('a','b'),'c');
 concat
--------
 abc
(1 row)

The other question by you was regarding concat_ws function, This function does not currently exist in Vertica.
I tried to mock the function using SQL UDF, this might help you, but this is again limited to only two columns.

create function CONCAT_WS(sep varchar, column1 varchar, column2 varchar)      return varchar
as
begin
return 
case when sep IS NULL then 'No seperator provided'
     when sep IS NOT NULL then CONCAT(CONCAT(column1,sep),column2)
     else 'NULL' 
END;
end;

Examples:

nnani=>SELECT CONCAT_WS(' ','a=apple','b=banana');
    CONCAT_WS
------------------
 a=apple b=banana
(1 row)

nnani=>SELECT CONCAT_WS('&','a=apple','b=banana');
    CONCAT_WS
------------------
 a=apple&b=banana
(1 row)

Hope this helps.

ahaldar1106

what to do when one of the columns is null?

Jim_Knicely

Refer to:

https://forum.vertica.com/discussion/241724/am-concatenating-multiple-rows-using-col1-col2-but-i-am-getting-null-as-one-of-the-columns