Oracle concatenate with conditions-CodePudding

I need to concatenate data from different column with comma in between. However, the column might also have null value, so the comma between is not needed anymore. Means that, it should have comma in between but if ADDR4 or ADDR5 has no data, then it should ignore the comma added

Here what I tried but it show the extra comma when the the last two columns has no data

SELECT ADDR1 ||','||ADDR2 ||','||ADDR3 ||','||ADDR4 ||','||ADDR5 AS Address, FROM ADDRDATA

output with extra comma

CodePudding user response：

Include CASE (or DECODE), e.g.

select addr1 || case when addr2 is not null then ','|| addr2 end
             || case when addr3 is not null then ','|| addr3 end
             || case when addr4 is not null then ','|| addr4 end
             || case when addr5 is not null then ','|| addr5 end as address
from addrdata

CodePudding user response：

A small correction to #Littlefoot. The query shared will return leading ',' when addr1 is null (or) consecutive columns at the start are null. A check of all the previous fields would be required in this case.

A possible workaround may be,

SELECT RTRIM ( addr1 
        || CASE WHEN addr2 IS NOT NULL THEN addr2 || ',' ELSE NULL END
        || CASE WHEN addr3 IS NOT NULL THEN addr3 || ',' ELSE NULL END
        || CASE WHEN addr4 IS NOT NULL THEN addr4 || ',' ELSE NULL END
        || CASE WHEN addr5 IS NOT NULL THEN addr5 || ',' ELSE NULL END, ',')
      AS address
  FROM addrdata

a work around using DECODE would be something like,

SELECT RTRIM (   DECODE (addr1, NULL, NULL, addr1 || ',')
              || DECODE (addr2, NULL, NULL, addr2 || ',')
              || DECODE (addr3, NULL, NULL, addr3 || ',')
              || DECODE (addr4, NULL, NULL, addr4 || ',')
              || DECODE (addr5, NULL, NULL, addr5 || ','),
              ','
             ) AS address
  FROM addrdata