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!
Concat more than two columns or values
+1You can concatenate columns as shown below instead of using concat function.
SELECT a||b||c from test;
SELECT a||b||c from test;
Works great, thanks!
+2Hi 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 varcharExamples:
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;
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.what to do when one of the columns is null?
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
