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?

Please replace the images with text (use ctrl-K to keep the format) — David דודו Markovitz, Dec 09 '16 at 20:38
this is a regex type parse - and please tell the table designer to never design like this again.. — Randy, Dec 09 '16 at 20:49

David דודו Markovitz · Answer 1 · 2016-12-10T07:10:13.293

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
 ;

edited Dec 10 '16 at 07:10

answered Dec 09 '16 at 21:05

David דודו Markovitz

42,900
6
64
88

I'm trying out this option and so far so good, just validating actual data on the table. – Zombraz Dec 09 '16 at 23:34

David דודו Markovitz · Answer 2 · 2016-12-10T07:09:01.847

1

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
;

edited Dec 10 '16 at 07:09

answered Dec 09 '16 at 21:49

David דודו Markovitz

42,900
6
64
88

Check this out. – David דודו Markovitz Dec 10 '16 at 05:59
is there any reason to use sys_guid() ? – Tymur Gubayev Dec 10 '16 at 06:54
@TymurGubayev - It prevents `ORA-01436: CONNECT BY loop in user data` – David דודו Markovitz Dec 10 '16 at 06:57

score 1 · Answer 3 · edited May 23 '17 at 12:34

1

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

edited May 23 '17 at 12:34

Community

1
1

answered Dec 09 '16 at 22:07

Kacper

4,798
2
19
34

1

No reason to concatenate everything, just to de-concatenate later. Much more efficient to split each string separately. – Dec 09 '16 at 22:32
Yes! When I concatenate it sends me an error that says ORA-01489: result of string concatenation is too long. – Zombraz Dec 09 '16 at 22:49
Looks like I need to change the data type to CLOB, since Varchar2 is very limited. I've been reading about using xmlagg, going to give it a try... – Zombraz Dec 09 '16 at 23:22
@Kacper The results are fine when my data set is small, but when I work with over 4000 rows it send me the ORA-01498 – Zombraz Dec 10 '16 at 00:03

score 1 · Accepted Answer · answered Dec 10 '16 at 07:08

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
 ;

Works really nice! And it sends me the results in seconds, I'm evaluating the output data, but everything seems to work just fine! Thank you so much! — Zombraz, Dec 12 '16 at 14:46

Substring in a column

4 Answers4