sql - How to remove duplicates from space separated list by Oracle regexp_replace? -

i have list called 'a b a c d'. expected result 'a b c d'. far web have found out

regexp_replace(l_user ,'([^,]+)(,[ ]*\1)+', '\1'); 

expression. , separated list. modification need done in order make space separated list. no need consider order.

if understand don't need replace ',' space, remove duplicates in smarter way.

if modify expression work space instead of ',', get

select regexp_replace('a b a c d' ,'([^ ]+)( [ ]*\1)+', '\1') dual 

which gives 'a b c d', not need.

a way needed result following, bit more complicated:

with string(s) ( select 'a b a c d' dual)         select listagg(case when rn = 1 str end, ' ') within group (order lev)     (             select str,  row_number() on (partition str order 1) rn, lev             (                 select trim(regexp_substr(s, '[^ ]+', 1, level)) str,                        level lev                   string                 connect instr(s, ' ', 1, level - 1) > 0                 )          ) 

my main problem here i'm not able build regexp checks non adjacent duplicates, need split string, check duplicates , aggregate again non duplicated values, keeping order.

if don't mind order of tokens in result string, can simplified:

with string(s) ( select 'a b a c d' dual) select listagg(str, ' ') within group (order 1) (         select distinct trim(regexp_substr(s, '[^ ]+', 1, level)) str           string         connect instr(s, ' ', 1, level - 1) > 0      )