How to remove duplicates from a space-separated list using Oracle regexp_replace?

Question

I have a list called "ABAAC D". My expected result is "ABC D". So far from the Internet I have discovered

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

Expression. But this is for a split list. What modification needs to be done to make it a spatially separated list. no need to consider order.

Answer 1

If I understand well, you just do not need to replace the "," with a space, but also remove duplicates in a more reasonable way.

If I change this expression to work with space instead of ',', I get

select regexp_replace('A B A A C D' ,'([^ ]+)( [ ]*\1)+', '\1') from dual

'A B A C D', , .

, :

with string(s) as ( select 'A B A A C D' from dual)    
    select listagg(case when rn = 1 then str end, ' ') within group (order by lev)
    from (
            select str,  row_number() over (partition by str order by 1) rn, lev
            from (
                SELECT trim(regexp_substr(s, '[^ ]+', 1, level)) str,
                       level as lev
                  FROM string
                CONNECT BY instr(s, ' ', 1, level - 1) > 0
                )
         )

, , , , , .

, :

with string(s) as ( select 'A B A A C D' from dual)
select listagg(str, ' ') within group (order by 1)
from (
        SELECT distinct trim(regexp_substr(s, '[^ ]+', 1, level)) as str
          FROM string
        CONNECT BY instr(s, ' ', 1, level - 1) > 0
     )

Answer 2

, ( , , ), , ), , . - row_number() .

with
     inputs ( str ) as ( select 'A B A A C D' from dual)
-- end test data; solution begins below this line
select listagg(token, ' ') within group (order by id) as new_str
from (
       select level as id, regexp_substr(str, '[^ ]+', 1, level) as token,
              row_number() over ( 
                             partition by regexp_substr(str, '[^ ]+', 1, level)
                             order by level ) as rn

       from   inputs
       connect by regexp_substr(str, '[^ ]+', 1, level) is not null
     )
where rn = 1
;

Answer 3

XQUERY?

select xmlquery('string-join(distinct-values(ora:tokenize(.," ")), " ")' passing  'A B A A C D' returning content) result  from dual