Oracle：使用分隔符连接，但前提是两个操作数都不为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11454136/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: Concat with delimiter, but only if both operands are NOT NULL
提问by GolezTrol
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').
因此，对于 的记录a='foo', b=NULL, c='bar'，我想得到结果abc='foo;bar'（不是'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.
我想要一个这样的功能concat_sep(a, b, ';')，只添加';' 如果 a 和 b 都不为空，则介于两者之间。
Of course, I can use nvl2 like this:
当然，我可以像这样使用 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)
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. ;-)
但是正如您所看到的，这段代码很快就会变得混乱，尤其是当您有超过 3 列并且您给它们指定了合理的名称而不是 a、b 和 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).
我找不到更短、更容易或更易读的方法，但我想在完全放弃之前我会在这里问一下（或者浪费时间自己编写这样的函数）。
采纳答案by Lukas Eder
I know you're using 10g, so that won't work. But for completeness, LISTAGG()handles NULLvalues "correctly". For that you'd have to update to 11g2, though:
我知道你使用的是 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;
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 :-)
现在我不确定这是否比您原来的示例好得多（更具可读性）:-)
回答by Tebbe
AFAIK, there's no succinct way to do this.
AFAIK，没有简洁的方法可以做到这一点。
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.
但这并不比你的例子好。
回答by Edoardo
select trim(';' from REGEXP_REPLACE (a || ';' || b || ';' || c , ';+' , ';')) abc
from Table1
