Oracle Regex remove duplicates

Question

I have a requirement to remove duplicate values from a comma separated string.

Input String: a,a,a,b,c,a,b
Expected output: a,b,c

What I have tried:

with ct(str) as(
select 'a,a,a,b,c,a,b' from dual
)
select REGEXP_REPLACE(str,'([^,]*)(,\1)+($|,)','\1\3') col from ct

Output: a,b,c,a,b

The above query can remove repetitive characters which are consecutive.

I know that the above requirement can be solved by creating a table out of the comma separated values and do a listagg on the distinct values.

Is it possible to achieve the above requirement using a single regex statement?.

Probably not. This question has been asked on SO (and other discussion boards) numerous times, and no one has found a solution with a single REGEXP call. Do you require a rigorous (mathematical) proof that such a solution does not exist, at least with Oracle's implementation of regular expressions? That may be pretty tough. — user5683823
– user5683823, Commented Jun 15, 2017 at 14:12
With .NET, PyPi regex, it is possible, but Oracle regex does not support lookarounds. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jun 15, 2017 at 14:22
@mathguy Thanks.. I will try to split the comma separated values to rows and select distinct values from them. — Sabin Jose
– Sabin Jose, Commented Jun 16, 2017 at 4:25

Uday Shankar · Accepted Answer · 2020-04-25 13:12:55Z

1

This should give you the required result:

     with borken as (SELECT distinct column_value as str,'1' cnt FROM 
     table(apex_string.split('a,a,a,b,c,a,b' ,','))  )
     select listagg(str,',') within group (order by cnt) from borken;

answered Apr 25, 2020 at 13:12

Uday Shankar

4781 gold badge4 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sabin Jose Over a year ago

Thanks. I know this can be achieved using an intermediate table. But I was looking for a solution implemented regex alone.

Collectives™ on Stack Overflow

Oracle Regex remove duplicates

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related