Am concatenating multiple rows using : col1||col2.... but I am getting null as one of the columns

is a null value. how to get not null column

Best Answer


  • Options
    Bryan_HBryan_H Vertica Employee Administrator

    COALESCE with blank. Replace null with your column name below:
    dbadmin=> select 'foo'||COALESCE(null,'')||'bar';


    (1 row)

  • Options
    ahaldar1106ahaldar1106 Vertica Customer

    It worked thanks.

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited July 2020

    @ahaldar1106: @Bryan_H's suggestion might be the better choice as COALESCE is an ANSI SQL-92 standard!

    dbadmin=> SELECT * FROM t;
     a | b | c
     A |   | B
    (1 row)
    dbadmin=> SELECT coalesce(a, '') || coalesce(b, '') || coalesce(c, '') abc FROM t;
    (1 row)

    See: https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Null/COALESCE.htm

  • Options
    marcothesanemarcothesane - Select Field - Administrator

    COALESCE() might be a little slower - as it has a variable number of parameters, and returns the first non-null in the list.
    IFNULL(), NVL() and ISNULL() are synonyms; all three take two arguments, and return the second argument if the first is NULL.

    You can expect functions with a fix number of arguments to have a less complex context, and be slightly faster ...

Leave a Comment

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