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

oracle, regex, sql

Solution

If I understand well you don't simply need to replace ',' with a space, but also to remove duplicates in a smarter way.

If I modify that expression to work with space instead of ',', I get

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

which gives `'A B A C D'`, not what you need.

A way to get your needed result could be the following, a bit more complicated:

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
                )
         )

My main problem here is that I'm not able to build a regexp that checks for non adjacent duplicates, so I need to split the string, check for duplicates and then aggregate again the non duplicated values, keeping the order.

If you don't mind the order of the tokens in the result string, this can be simplified:

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
     )

Problem

I have a list called 'A B A A C D'. My expected result is 'A B C D'. So far from web I have found out ``` regexp_replace(l_user ,'([^,]+)(,[ ]*\1)+', '\1'); ``` Expression. But this is for , separated list. What is the modification need to be done in order to make it space separated list. no need to consider the order.

Original source