CODEWITHSUNDEEP
sqloracle-databaseconcatenationdelimiteroracle18c
User1974
User1974

Reputation: 386

Dynamic concatenation with delimiter (ignore nulls)

I have a table that has nulls:

STREET_NAME LOCATION CULVERT_TYPE CULVERT_WIDTH CULVERT_MATERIAL
MAIN ST FROM ST A TO ST B SEWER STORM \ CULVERT 1750 CORRUGATED STEEL PIPE
SIDE ST FROM ST C TO ST D SEWER STORM \ CULVERT CORRUGATED STEEL PIPE
ARENA 1 SEWER STORM \ CULVERT 300
SEWER STORM \ CULVERT 500
. 
with cte as (
select 'MAIN ST' as STREET_NAME, 'FROM ST A TO ST B' as LOCATION, 'SEWER STORM \ CULVERT' as CULVERT_TYPE,          1750 as CULVERT_WIDTH, 'CORRUGATED STEEL PIPE' as CULVERT_MATERIAL
from dual
union all
select 'SIDE ST' as STREET_NAME, 'FROM ST C TO ST D' as LOCATION,  'SEWER STORM \ CULVERT' as CULVERT_TYPE,          null as CULVERT_WIDTH, 'CORRUGATED STEEL PIPE' as CULVERT_MATERIAL
from dual
union all
select null as STREET_NAME,      'ARENA 1' as LOCATION,            'SEWER STORM \ CULVERT' as CULVERT_TYPE,          300 as CULVERT_WIDTH,  null as CULVERT_MATERIAL
from dual
union all
select null as STREET_NAME,      null as LOCATION,                 'SEWER STORM \ CULVERT' as CULVERT_TYPE,          500 as CULVERT_WIDTH,  '' as CULVERT_MATERIAL
from dual
union all
select null as STREET_NAME,      null as LOCATION,                 null as CULVERT_TYPE,                              null as CULVERT_WIDTH, null as CULVERT_MATERIAL
from dual
)
select * from cte

I want to concatenate the values into a pipe-delimited | column (and ignore nulls).

CULVERT DESCRIPTION
----------------------
MAIN ST | FROM ST A TO ST B | SEWER STORM \ CULVERT | 1750 | CORRUGATED STEEL PIPE
SIDE ST | FROM ST C TO ST D | SEWER STORM \ CULVERT | CORRUGATED STEEL PIPE
ARENA 1 | SEWER STORM \ CULVERT | 300
SEWER STORM \ CULVERT | 500
<null>

Question:

Is there a way to do a dynamic concatenation like that in Oracle 18c, while ignoring nulls?

It would be ideal if the solution were a simple function that allowed for easy reading, such as:

dyamic_concat( delimiter , parameters [ column1 , column2, column3 ]... )

Upvotes: 0

Views: 99

Answers (1)

Popeye
Popeye

Reputation: 35910

Use the case .. when as follows:

Select 
  Trim('|' from 
  case when street_name is not null then street_name end
  || case when location is not null then '|' || location end
  || case when culvert_type is not null then '|' || culvert_type end
  || case when culvert_width is not null then '|' || culvert_width end
  || case when culvert_material is not null then '|' || culvert_material end)
 From your_table;

You can also use regexp_replace and trim as follows:

Select 
 Trim('|' from 
      regexp_replace( street_name || '|' ||  location || '|' || 
      culvert_type || '|' || 
      culvert_width || '|' || culvert_material ,'[\|]+','|'))
From your_table;

Upvotes: 2

Related Questions