I have CLOB column which contains comments entered by multiple people multiple times. All concatenated and stored in CLOB.
I want to get comments entered by one person from this Column and get all comments.
Following is comments from CLOB:-
"
*** EMP1 - 02/15/16 09:32 PST ***Sallary Approved - Emp1EMP2***** EMP2 - 02/19/16 07:59 ***What is the value of raise. *** EMP2 - 02/19/16 7:59 PST ***confidential***** EMP1 - 02/23/16 21:31 PST ***Details can be shared - emp1"\
---------------------
In the above text , I want to extract strings between EMP1 and emp for all occurances.
I can use substr. But is there a way to do this without using loops in single regular expression.
I tried the following for one occurance.
ex. Table temp1 contains comments.
select substr(to_char(comments),INSTR(to_char(a.comments),'EMP1',1,1),(INSTR(to_char(a.comments),'emp1',1,1)-INSTR(to_char(a.comments),'EMP1',1,1))+4) from temp1 where workflow_user='EMP1';
I'm not clear what output you want. Do you want a separate line for each section starting EMPn and finishing at the next empn?
If so, you need a row generator to create the lines:
with rws as (
select level lev from dual
connect by level <= (select max(regexp_count(comments, 'EMP1')) from temp)
)
select * from rws;
Then cross join this with the table. You can then get pass the level (lev) to substr/instr to return the Nth occurrence:
create table temp (
comments clob
);
insert into temp values (
'*** EMP1 - 02/15/16 09:32 PST ***Sallary Approved - emp1EMP2***** EMP2 - 02/19/16 07:59 ***What is the value of raise. *** EMP2 - 02/19/16 7:59 PST ***confidential***** EMP1 - 02/23/16 21:31 PST ***Details can be shared - emp1'
);
with rws as (
select level lev from dual
connect by level <= (select max(regexp_count(comments, 'EMP1')) from temp)
)
select substr (
to_char ( comments ) ,
instr ( to_char ( a.comments ) ,'EMP1',1, lev ) ,
( instr ( to_char ( a.comments ) ,'emp1',1, lev ) -
instr ( to_char ( a.comments ) ,'EMP1',1, lev ) )+4
)
from rws, temp a;
SUBSTR(TO_CHAR(COMMENTS),INSTR(TO_CHAR(A.COMMENTS),'EMP1',1,LEV),(INSTR(TO_CHAR(A.COMMENTS),'EMP1',1,LEV)-INSTR(TO_CHAR(A.COMMENTS),'EMP1',1,LEV))+4)
----------------------------------------------------------------------------------------------------------------------------------------------------------------
EMP1 - 02/15/16 09:32 PST ***Sallary Approved - emp1
EMP1 - 02/23/16 21:31 PST ***Details can be shared - emp1 Note: in your example the first entry finished with Emp1 instead of emp1. I'm not sure whether this is intentional or not. I've gone with all lowercase for simplicity.