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

Answers

  • Bryan_HBryan_H Employee

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

    ?column?

    foobar
    (1 row)

  • It worked thanks.

  • Jim_KnicelyJim_Knicely Administrator
    edited July 14

    @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;
     abc
    -----
     AB
    (1 row)
    

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

  • 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
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.