oracle sql help ?

[bandababu]

two strings madhya lo unna text motham extract cheyyali ?

CLOB column lo text ila unda

 

A boy gains 10 lbs in 2 years while his friends gained only 

5 lbs . He ate 400-500 calories of food in every meal.

 

while and meal madhya lo unna text motham kavali ? substr and instr functions tho ela rayali ?

 

column lo data vary avuthundi. so positions consistent ga undadu

[former]

First, you need convert CLOB to varchar with DBMS_LOB.SUBSTR function

 

SUBSTR(varchar_string, INSTR(varchar_string,'while')+5, INSTR(varchar_string,'meal'))

This works in Oracle.

[former]

Just found in google, you can directly use DBMS_LOB.SUBSTR and DBMS_LOB.INSTR functions directly on CLOB's.

Try like below:

DBMS_LOB.SUBSTR(CLOB_FIELD,DBMS_LOG.INSTR(CLOB_FIELD,'while')+5,DBMS_LOB.INSTR(CLOB_FIELD,'meal'))