We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Concat more than two columns or values — Vertica Forum

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!
  • Navin_CNavin_C Vertica Customer

    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.
  • ahaldar1106ahaldar1106 Vertica Customer

    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