Substring in a column

Question

I have a column that has several items in which I need to count the times it is called, my column table looks something like this:

Table Example

Id_TR               Triggered
--------------      ------------------
A1_6547             R1:23;R2:0;R4:9000
A2_1235             R2:0;R2:100;R3:-100
A3_5436             R1:23;R2:100;R4:9000
A4_1245             R2:0;R5:150

And I would like the result to be like this:

Expected Results

Triggered          Count(1)
---------------    --------
R1:23               2
R2:0                3
R2:100              2
R3:-100             1
R4:9000             2
R5:150              1

I've tried to do some substring, but cant seem to find how to solve this problem. Can anyone help?

Answer 1

This solution is X3 times faster than the CONNECT BY solution

performance: 15K records per second

with        cte (token,suffix)
            as 
            (
                select      substr(triggered||';',1,instr(triggered,';')-1)     as token
                           ,substr(triggered||';',instr(triggered,';')+1)       as suffix

                from        t

                union all

                select      substr(suffix,1,instr(suffix,';')-1)     as token
                           ,substr(suffix,instr(suffix,';')+1)       as suffix

                from        cte

                where       suffix is not null

            )

 select     token,count(*)
 from       cte
 group by   token
 ;          

Answer 2

with x as (
   select listagg(Triggered, ';') within group (order by Id_TR) str from table
)
select regexp_substr(str,'[^;]+',1,level) element, count(*)
  from x
  connect by level <= length(regexp_replace(str,'[^;]+')) + 1
  group by regexp_substr(str,'[^;]+',1,level);

First concatenate all values of triggered into one list using listagg then parse it and do group by.

Another methods of parsing list you can find here or here