sql - Search for a particular string in Oracle clob column -

how particular string clob column?

i have data below stored in clob column called product_details

calculation=[n]new.product_no=[t9856]  old.product_no=[t9852].... -- other text 

i search string new.product_no column product_details

i have tried as

select * my_table dbms_lob.instr(product_details,'new.product_no')>=1 

the above fetches full text table.

any highly appreciable.

regards

use dbms_lob.instr , dbms_lob.substr, regular instr , subststr functions.
@ simple example:

sql> create table t_clob(   2    id number,   3    cl clob   4  );  tabela zosta│a utworzona.  sql> insert t_clob values ( 1, ' xxxx abcd xyz qwerty 354657 [] ' );  1 wiersz zosta│ utworzony.  sql> declare   2    number;   3  begin   4    in 1..400 loop   5        update t_clob set cl = cl || ' xxxx abcd xyz qwerty 354657 [] ';   6    end loop;   7    update t_clob set cl = cl || ' calculation=[n]new.product_no=[t9856] old.product_no=[t9852].... -- other text ';   8    in 1..400 loop   9        update t_clob set cl = cl || ' xxxx abcd xyz qwerty 354657 [] ';  10    end loop;  11  end;  12  /  procedura pl/sql zosta│a zako˝czona pomyťlnie.  sql> commit;  zatwierdzanie zosta│o uko˝czone. sql> select length( cl ) t_clob;  length(cl) ----------      25717  sql> select dbms_lob.instr( cl, 'new.product_no=[' ) t_clob;  dbms_lob.instr(cl,'new.product_no=[') -------------------------------------                                 12849  sql> select dbms_lob.substr( cl, 5,dbms_lob.instr( cl, 'new.product_no=[' ) + length( 'new.product_no=[') ) new_product   2  t_clob;  new_product -------------------------------------------------------------------------------- t9856