CODEWITHSUNDEEP
sqlregexstringoracle-database
Ariel
Ariel

Reputation: 978

Regex in SQL not grouping correctly

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?

Upvotes: 0

Views: 51

Answers (1)

Aleksej
Aleksej

Reputation: 22949

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.

Upvotes: 1

Related Questions