How to remove duplicates from comma separated list by regex in Oracle but I don't want duplicates values?

Question

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;

Answer 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