I am trying to create a function that searches a CLOB for a specific term. In this case 'hello world' ONLY!!!
Note some of the text within the CLOB can contain LF and some may not. I provided some sample data off both.
In this particular example I'm looking to search through the CLOB to find the starting and ending positions of the phrase 'hello world' ONLY.
I know to find the starting position I need to use the INSTR command and the ending position should be INSTR+LENGTH of the phrase I'm searching for.
I haven't added that code yet as I am unable to get the parsing working correctly yet.
If you run the sample data I provided you can I'm getting back more than the phrase 'hello world'
Once the parse works, I'm looking to generate the following output (see below).
id start_pos end_pos search_term
Note in my function the number 4000 is hard coded as I recall that being the max limit of a VARCHAR2. I think it's been extended to 32767 in 12c and up but that should be a non issue here as I only have a small amount of data.
Any help would be greatly appreciated. Below is my test CASE and im testing on livesql so my environment can be replicated if needed.
create table t ( x int primary key, y clob );
insert into t values ( 1, 'line 1 line 2 line 3 line 4 line 5 hello world' );
insert into t values ( 2, 'line 1 hello world line 2 line 3 line 4 line 5 hello world' );
insert into t values ( 3, 'line 1 hello world line 2 line 3 line 4 line 5
hello world' );
declare
l_text long;
begin
for i in 1 .. 3000
loop
l_text := l_text || 'line ' || i || chr(10);
end loop;
l_text := l_text || 'hello world 3001';
insert into t values ( 4, l_text );
l_text := l_text || chr(10) || 'line 3002';
insert into t values ( 5, l_text );
l_text := 'hello world 0' || chr(10) || l_text;
insert into t values ( 6, l_text );
end;
create or replace function search_clob( p_lob in clob, p_what in varchar2 ) return varchar2
as
l_text long;
l_instr number;
begin
l_instr := dbms_lob.instr( p_lob, p_what );
l_instr := dbms_lob.instr( p_lob, chr(10), l_instr );
if ( l_instr = 0 ) then l_instr := dbms_lob.getlength(p_lob); end if;
if ( l_instr < 4000 )
then
l_text := rtrim(chr(10) || dbms_lob.substr( p_lob, l_instr, 1 ), chr(10));
else
l_text := rtrim(chr(10) || dbms_lob.substr( p_lob, 4000, l_instr-3999 ), chr(10));
end if;
return substr( l_text, instr(l_text,chr(10), -1, 1 )+1 );
end;
select x,search_clob( y, 'hello world' ) y, dbms_lob.getlength(y) len
from t
where dbms_lob.instr( y, 'hello world' ) > 0;