Oracle: Concat with delimiter, but only if both operands are NOT NULL

I want to select a concatenation of a couple of fields, but with a separator between them. The separator should only be there if both operands are not null.

So for a record with `a='foo', b=NULL, c='bar'`, I want to get the result `abc='foo;bar'` (not `'foo;;bar'`).

I would like to have a function like `concat_sep(a, b, ';')` that only adds the ';' inbetween if both a and b are not null.

Of course, I can use nvl2 like this:

select
a, b, c,
substr(abc, 1, length(abc) - 1) as abc
from
(select
a, b, c,
nvl2(a, a || ';', '') || nvl2(b, b || ';', '') || nvl2(c, c || ';', '') as abc
from
Table1)

But as you can see, this code becomes cloggy soon, especially when you got more than 3 columns and you've given them sensible names instead of a, b and c. ;-)

I couldn't find a shorter, easier or more readable way, but I thought I'd ask here before giving up entirely (or waste time writing such a function myself).

AFAIK, there's no succinct way to do this.

In the past, I've resorted to

SELECT a
|| DECODE(b
, NULL, NULL
, ';' || b)
|| DECODE(c
, NULL, NULL
, ';' || c)
|| DECODE(d
, NULL, NULL
, ';' || d)
...
FROM table1

but that's no better than your example.

I know you're using 10g, so that won't work. But for completeness, [`LISTAGG()`][1] handles `NULL` values "correctly". For that you'd have to update to 11g2, though:

-- Some sample data, roughly equivalent to yours
with t as (
select 'foo' as x from dual union all
select null from dual union all
select 'bar' from dual
)
-- Use the listagg aggregate function to join all values
select listagg(x, ';') within group (order by rownum)
from t;

Or a bit more succinct, if you want to list columns from a table:

-- I use SYS.ORA_MINING_VARCHAR2_NT as a TABLE TYPE. Use your own, if you prefer
select listagg(column_value, ';') within group (order by rownum)
from table(ORA_MINING_VARCHAR2_NT('foo', null, 'bar'));

Or against an actual table:

select listagg(column_value, ';')
within group (order by rownum)
from Table1
cross join table(ORA_MINING_VARCHAR2_NT(Table1.a, Table1.b, Table1.c))
group by Table1.id;

Now I'm not sure if this is so much better (more readable) than your original example :-)

[1]:

[To see links please register here]

select trim(';' from REGEXP_REPLACE (a || ';' || b || ';' || c , ';+' , ';')) abc
from Table1

Not all fields will have data sequentially. This is for fields that are blank and without spaces:

1. Evaluate if the field is blank, if so concatenate an empty field so it is ignored and if blank, concatenate the field with a space on the end.

2. Then [TRIM] spaces (leading and trailing), then [REPLACE] spaces with your delimiter.

You can get creative with this until Oracle gives a function to do this.

Code snippet:

SELECT
f1,f2,f3,f4
, REPLACE (TRIM(
CASE WHEN f1 IS NULL THEN '' ELSE f1||' ' END||
CASE WHEN f2 IS NULL THEN '' ELSE f2||' ' END||
CASE WHEN f3 IS NULL THEN '' ELSE f3||' ' END||
CASE WHEN f4 IS NULL THEN '' ELSE f4||' ' END),' ','|') concat_result
from
(SELECT
'I' f1
,'want' f2
,'' f3
,'concat' f4
FROM dual) c'