Hi Team,
I have a problem. like i have table which contains a CLOB column. From that i need to extract exactly a string of word. I will give an example on how it will be below.
Let Table T_OBJ have a column WORK_LOG which is a CLOB.
The data in the CLOB is huge but from that i need to extract a string, containing name which falls exactly after a WORD
Create table T_OBJ(SNO number, WORK_LOG clob);
Insert into T_OBJ values (1,"1263636000 AR_ESCALATOR Modified By XXXXXXXXX XXXX.This has been Auto Closed.Change has been closed")
This is how the sample data will be. But what i found more is there are delimiters in the text. A delimiter is there exactly after name i.e., XXXXXXXX XXXX(delimiter)
My output should be only name i.e., XXXXXXXX XXXX, which falls immediately after the text 'Modified By'
I tried like this
select dbms_lob.substr( WORK_LOG, 50,dbms_lob.instr( WORK_LOG, 'Modified by ' ))
from T_OBJ;
Can anyone help me please. I am using ORACLE 11g.