Options
Concat more than two columns or values
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!
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!
0
Comments
SELECT a||b||c from test;
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');The other question by you was regarding concat_ws function, This function does not currently exist in Vertica.concat
--------
abc
(1 row)
I tried to mock the function using SQL UDF, this might help you, but this is again limited to only two columns. Examples:
nnani=>SELECT CONCAT_WS(' ','a=apple','b=banana');
Hope this helps.CONCAT_WS
------------------
a=apple b=banana
(1 row)
nnani=>SELECT CONCAT_WS('&','a=apple','b=banana');
CONCAT_WS
------------------
a=apple&b=banana
(1 row)
what to do when one of the columns is null?
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