user761758 user761758 - 1 year ago 79
SQL Question

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.

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.

Answer Source

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 || ')' emd)
       ) as concatenated
from table1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download