5 Replies - 917 Views - Last Post: 30 August 2016 - 10:30 AM

#1 blues49  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 30-August 16

Concatenate with brackets where nulls are involved

Posted 30 August 2016 - 08:56 AM

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



SELECT "ID",
          "PART",
          "SECTION",
          "SUB1",
          "SUB2",
          "SUB3",
          "SUB4",
           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

Or 113.450(h)(2)(iv)() when there is one null value.

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


Thank you.

This is in an Oracle 12c database.......

Is This A Good Question/Topic? 0
  • +

Replies To: Concatenate with brackets where nulls are involved

#2 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6403
  • View blog
  • Posts: 25,880
  • Joined: 12-December 12

Re: Concatenate with brackets where nulls are involved

Posted 30 August 2016 - 09:23 AM

I don't use Oracle but there are a number of functions that can help with NULL, detailed here. NVL2 looks promising, as you could return the bracketed value or an empty string.

Wouldn't it be misleading, though? If the 2nd value is NULL the 3rd value would appear to take its place. A simple placeholder such as (-) or (n/a) would usually be preferable.

Topic moved to the Oracle subforum.
Was This Post Helpful? 1
  • +
  • -

#3 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2340
  • View blog
  • Posts: 9,388
  • Joined: 03-December 12

Re: Concatenate with brackets where nulls are involved

Posted 30 August 2016 - 09:51 AM

For the statisticians standpoint. A null value is still an important value. Have you looked at an if statement? I assume Oracle has them, everyone else does.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6403
  • View blog
  • Posts: 25,880
  • Joined: 12-December 12

Re: Concatenate with brackets where nulls are involved

Posted 30 August 2016 - 10:07 AM

I think a CASE statement could be used, something like CASE WHEN x IS NULL '' ELSE '(' || x || ')' END ... It is probably easier to read (if formatted nicely) but I think I'd rather use a function ;)
Was This Post Helpful? 1
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6995
  • View blog
  • Posts: 14,628
  • Joined: 16-October 07

Re: Concatenate with brackets where nulls are involved

Posted 30 August 2016 - 10:11 AM

As a quick proof of conecpt, this seemed to work:
select a.id, 
    nvl2(a.c1, '('||a.c1||')', '')
      || nvl2(a.c2, '('||a.c2||')', '') as x
  from (
    select 1 as id, 'A' as c1, 'Alice' as c2 from dual
    union select 2, null, 'Bob' from dual 
    union select 3, 'C', null from dual
    union select 4, null, null from dual 
    ) a



Alternately, with the more standard CASE:
select a.id, 
  case when a.c1 is not null then '('||a.c1||')' else '' end
  || case when a.c2 is not null then '('||a.c2||')' else '' end
  from (
    select 1 as id, 'A' as c1, 'Alice' as c2 from dual
    union select 2, null, 'Bob' from dual 
    union select 3, 'C', null from dual
    union select 4, null, null from dual 
    ) a


Was This Post Helpful? 0
  • +
  • -

#6 blues49  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 30-August 16

Re: Concatenate with brackets where nulls are involved

Posted 30 August 2016 - 10:30 AM

View Postandrewsw, on 30 August 2016 - 10:07 AM, said:

I think a CASE statement could be used, something like CASE WHEN x IS NULL '' ELSE '(' || x || ')' END ... It is probably easier to read (if formatted nicely) but I think I'd rather use a function ;)/>



Perfect... Thank you!!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1