Oracle: Concat with delimiter, but only if both operands are NOT NULL
select trim(';' from REGEXP_REPLACE (a || ';' || b || ';' || c , ';+' , ';')) abc
from Table1
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()
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 :-)