Oracle: Concat with delimiter，但仅当两个操作数都不为NULL时

Question

我想要选择两个字段的串联，但它们之间要有分隔符。只有当两个操作数都不为空时，才应该有分隔符。

因此，对于使用a='foo', b=NULL, c='bar'的记录，我希望获得结果abc='foo;bar' (而不是'foo;;bar')。

我希望有一个像concat_sep(a, b, ';')这样的函数，如果a和b都不为空，它只在a和b之间添加';‘。

当然，我可以这样使用nvl2：

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)

但正如您所看到的，这段代码很快就会变得混乱，特别是当您有3个以上的列，并且您为它们指定了合理的名称，而不是a、b和c。;-)

我找不到一种更短、更简单、更易读的方法，但我想在完全放弃之前(或者自己浪费时间编写这样的函数)，我应该在这里问一下。

Answer 1

我知道您使用的是10g，所以这不会起作用。但是为了完整性，LISTAGG()“正确”地处理了NULL的值。要做到这一点，你必须更新到11g2：

-- 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;

或者更简洁一点，如果您想列出表中的列：

-- 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'));

或与实际的表进行比较：

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;

现在我不确定这是否比你原来的例子更好(更具可读性) :-)

Answer 2

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

Answer 3

AFAIK，没有简洁的方法可以做到这一点。

在过去，我曾求助于

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

但这并不比你的例子好多少。