Oracle 使用REGEXP_REPLACE删除重复项

排序输入:

select regexp_replace( 'three0, three, two, two, two2, two3, three, three, three, one1, one1', '([^,]+)(,\1)+(,|$)', '\1\3') from dual;

three0, three, two, two2, two3, three, one1

未排序输入:

select regexp_replace( 'three0, three, two,  two2, two3, three, three, three, one1, one1, two', '([^,]+)(,\1)+(,|$)', '\1\3') from dual;

three0, three, two,  two2, two3, three, one1, two

如果输入列表未排序，如何获得唯一结果？

我不知道使用正则表达式在不进行大量数据传递的情况下执行此操作的方法。

例如，以下有两个重复:

one,two,two,one

剥离多余的叶子:

one,two

但是，为了将最终的 “一个” 与第一个匹配，您还需要将两者之间进行匹配。但是现在你已经到了尽头了!

所以你需要回到开始，把第二个 “两个” 和第一个匹配起来。

相反，使用您最喜欢的CSV到行方法拆分您的字符串。然后将这个不同的结果与listagg粘合在一起:

with str as (
  select 'three0, three, two, two, two2, two3, three, three, three, one1, one1' s 
  from   dual
), rws as (
  select distinct trim(regexp_substr(s, '[^,]+', 1, r.rn)) s
  from   str, lateral (
    select level rn from dual 
    connect by level <= length (regexp_replace(s, '[^,]+')) + 1
  ) r
)
  select listagg(s, ',') within group (order by s) dist
  from   rws;

DIST                              
one1,three,three0,two,two2,two3