Search code examples
stringoracle-databaseclob

Check for a string in a CLOB

i want to search for a string in a CLOB:

some thing like id_name_2569

i get all my IDs i need like this: 

select project_line_id as ID
from tbl1
where art_id in (
                  select art_id
                  from tbl2
                  where type = 3
                 );

and i search in this table: A1 is a CLOB field

select * from tbl3 where dbms_lob.instr(A1, ID)>0;

obviously it isnt working i know, is here a way i could do this?

Solution

  • something like this should work:

    select tbl3.*
  from tbl1
       inner join tbl2
               on tbl2.art_id = tbl1.art_id
       inner join tbl3
               on tbl3.a1 like '%' || tbl1.project_line_id || '%'
 where tbl2.type = 3;