How to remove duplicates from comma separated list

问题:

This question already has an answer here:

• How to remove duplicates from space separated list by Oracle regexp_replace? [duplicate] 3 answers
• How to remove duplicates from comma separated list by regexp_replace in Oracle? 2 answers

I have this string

ABCD1234, XYZ, ABCD1234, ABCD1234C, ABCD1234, abc, abcX, 1234U, 1234

and I want, but I don't want duplicates values

ABCD1234, XYZ, ABCD1234C, abc, abcX, 1234U, 1234,

I'm using below regex

select regexp_replace (
    'ABCD1234, XYZ, ABCD1234, ABCD1234C, ABCD1234, abc, abcX, 1234U, 1234',
     '([^,]+)(,\1)+', '\1'
 ) test
from dual;

回答1:

Try this, as per article http://www.dba-oracle.com/t_extract_comma_delimited_strings_oracle_sql.html:

select distinct str from
(select regexp_substr ('ABCD1234, XYZ, ABCD1234, ABCD1234C, ABCD1234, abc, abcX, 1234U, 1234', '[^, ]+',1, rownum) str 
from dual 
connect by level <= regexp_count ('ABCD1234, XYZ, ABCD1234, ABCD1234C, ABCD1234, abc, abcX, 1234U, 1234', '[^, ]+')) v;

Fiddle: http://sqlfiddle.com/#!4/c858d/5

回答2:

Here's one option:

SQL> with test (id, col) as
  2    (select 1, 'ABCD1234, XYZ, ABCD1234, ABCD1234C, ABCD1234, abc, abcX, 1234U, 1234' from dual union
  3     select 2, '111, 222, 111, ABCX2, FFE, 222' from dual
  4    ),
  5  t_rows as
  6    (select id, regexp_substr(col, '[^, ]+', 1, column_value) col
  7     from test,
  8          table(cast(multiset(select level from dual
  9                              connect by level <= regexp_count(col, ',') + 1)
 10                              as sys.odcinumberlist))
 11    ),
 12  t_distinct as
 13    (select distinct id, col
 14     from t_rows
 15    )
 16  select id, listagg(col, ', ') within group (order by null) col
 17  from t_distinct
 18  group by id;

        ID COL
---------- --------------------------------------------------
         1 1234, 1234U, ABCD1234, ABCD1234C, XYZ, abc, abcX
         2 111, 222, ABCX2, FFE

SQL>