CODEWITHSUNDEEP
sqloracle-database
user761758
user761758

Reputation: 585

Oracle 12c Concatenate with brackets where nulls are involved

We have a set of columns within a table we need to concatenate, and we need brackets around the third, fourth, fifth and sixth value, but also need nothing to appear if the column is null.

   SELECT "ID",
             NVL(PART || '.'|| SECTION ||'(' ||SUB1||')'|| '(' ||SUB2|| ')'     || '('||SUB3||')' || '('||SUB4||')', '')  as concatenated
              FROM table1;

Places the values exactly right as long as there are values. When any one or more columns return null, we are getting an empty set of brackets for each null value.

Such as: 113.203()()()() when there are four null values in this case we would need: 113.203

Or 113.450(h)(2)(iv)() when there is one null value. here the desired results would be 113.450(h)(2)(iv)

How can I change the script to leave out all the empty brackets when a null value is returned?

Thank you.

Upvotes: 0

Views: 606

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Hmmm, I think you want:

select id,
       (part || '.' || section ||
        (case when sub1 is not null then '(' || sub1 || ')' end) ||
        (case when sub2 is not null then '(' || sub2 || ')' end) ||
        (case when sub3 is not null then '(' || sub3 || ')' end) ||
        (case when sub4 is not null then '(' || sub4 || ')' end)
       ) as concatenated
from table1;

Upvotes: 2

Related Questions