Regular Expression

Question

I have data like this:

A:123, A:983, A:122, B:232, B:392, C:921, D:221, D:121, D:838

And I want to have my result like

A:123, 983, 122, B:232, 392, C:921, D:221, 121, 838

Can anyone please suggest?

Answer 1

You can use regexp_substr() and listagg() functions

with connect by level <= regexp_count(':') as

with t(str) as
(
 select 'A:123, A:983, A:122, B:232, B:392, C:921, D:221, D:121, D:838' from dual
), t2 as
( 
select level as rn,
       regexp_substr(str,'([[:alpha:]]+)',1,level) as letter, 
       regexp_substr(str,'(\d)+',1,level) as num
  from t
connect by level <= regexp_count(str,':')  
), t3 as
(
select letter||':'||listagg(num,',') within group (order by rn) as str
  from t2
 group by letter
)
select listagg(str,',') within group (order by substr(str,1,1)) as str
  from t3;

STR
-------------------------------------------
A:123,983,122,B:232,392,C:921,D:221,121,838

Demo

Answer 2

You do not need regular expressions as it can be done with standard string functions:

Oracle Setup:

CREATE TABLE test_data ( value ) AS
SELECT 'A:123, A:983, A:122, B:232, B:392, C:921, D:221, D:121, D:838' FROM DUAL

Query:

WITH rsqfc ( id, value, spos, sep, epos ) AS (
  SELECT ROWNUM,
         value,
         1,
         INSTR( value, ':', 1 ),
         INSTR( value, ', ', 1 )
  FROM   test_data
UNION ALL
  SELECT id,
         value,
         epos + 2,
         INSTR( value, ':', epos + 2 ),
         INSTR( value, ', ', epos + 2 )
  FROM   rsqfc
  WHERE  epos > 0
),
items ( id, prefix, value ) AS (
  SELECT id,
         SUBSTR( value, spos, sep - spos ),
         CASE
           WHEN epos > 0
           THEN SUBSTR( value, sep + 1, epos - sep - 1 )
           ELSE SUBSTR( value, sep + 1 )
         END
  FROM   rsqfc
),
item_groups ( id, prefix, grouped_value ) AS (
  SELECT id,
         prefix,
         LISTAGG( value, ',' ) WITHIN GROUP ( ORDER BY ROWNUM )
  FROM   items
  GROUP BY id, prefix
)
SELECT LISTAGG( prefix || ':' || grouped_value, ', ' )
         WITHIN GROUP ( ORDER BY prefix ) AS value
FROM   item_groups
GROUP BY id

Output:

| VALUE                                          |
| :--------------------------------------------- |
| A:123,983,122, B:232,392, C:921, D:221,121,838 |

db<>fiddle here