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!

Comments

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

    SELECT a||b||c from test;
  • Works great, thanks!

  • 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.
  • what to do when one of the columns is null?

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.