Regex in SQL not grouping correctly

Question

I am using regex to string unique sources. My issue is the sources are merging:

i.e. sources [manager, ream, ream, ream]

Needed output [manager, ream]

Output receiving [manageream, ream]

SELECT regexp_replace( listagg(c.source, ',')
within group(order by c.person_no) , 
'([^,]+)(,\1)+', '\1') source 
FROM table c

How do I fix my above code to get the needed output?

The real problem is listagg. You can do this more easily without it. — Dan
– Dan, Commented May 16, 2016 at 13:25

Aleksej · Accepted Answer · 2016-05-16 15:19:53Z

1

If I understand your need, you may simply need DISTINCT:

SQL> create table test(source) as (
  2  select 'manager' from dual union all
  3  select 'ream'    from dual union all
  4  select 'ream'    from dual union all
  5  select 'ream'    from dual
  6  );

Table created.

SQL> select listagg(source, ',') within group (order by 1)
  2  from (
  3          select distinct source
  4          from test
  5       );

LISTAGG(SOURCE,',')WITHINGROUP(ORDERBY1)
----------------------------------------------------------------------------
manager,ream

SQL>

The inner query is used to only get the distinct values, while the outer makes the concatenation; you can rewrite this in different ways, I believe this is one of the most readable.

edited May 16, 2016 at 15:19

answered May 16, 2016 at 13:32

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

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

2 Comments

Ariel Over a year ago

hi thank you for the solution you provided, however I am still getting (manager, ream, ream) having "ream" duplicated in the result

Aleksej Over a year ago

what if you execute select distinct source from table? maybe you have similar but not equal values, for example due to blank chars?

Collectives™ on Stack Overflow

Regex in SQL not grouping correctly

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related