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 is just for learning purposes.
Check my other solutions.

performance: 1K records per second

select      x.triggered
           ,count(*)

from        t
           ,xmltable 
            (
                '/r/x'
                passing xmltype('<r><x>' || replace(triggered,';', '</x><x>') || '</x></r>')
                columns triggered varchar(100) path '.'
            ) x

 group by   x.triggered
 ;

Answer 2

This is a fair solution.

performance: 5K records per second

select      triggered
           ,count(*)            as cnt 

from       (select      id_tr
                       ,regexp_substr(triggered,'[^;]+',1,level) as triggered

            from        t

            connect by      id_tr  = prior id_tr
                        and level <= regexp_count(triggered,';')+1
                        and prior sys_guid() is not null
            ) t

group by    triggered
;

Answer 3

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 4

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