How to get a particular string from a clob column?
I have data as below which is stored in clob column called product_details
CALCULATION=[N]NEW.PRODUCT_NO=[T9856]
OLD.PRODUCT_NO=[T9852].... -- with other text
I would like to search for string NEW.PRODUCT_NO
from column product_details
I have tried as
select * from my_table
where dbms_lob.instr(product_details,'NEW.PRODUCT_NO')>=1
The above fetches full text from my table.
Any help is highly appreciable.
Regards
You can just CAST your CLOB value into a VARCHAR value and make your querie like a
ok, you may use substr in correlation to instr to find the starting position of your string
select
dbms_lob.substr(
product_details,
length('NEW.PRODUCT_NO'), --amount
dbms_lob.instr(product_details,'NEW.PRODUCT_NO') --offset
)
from my_table
where dbms_lob.instr(product_details,'NEW.PRODUCT_NO')>=1;
Below code can be used to search a particular string in Oracle clob column
select *
from RLOS_BINARY_BP
where dbms_lob.instr(DED_ENQ_XML,'2003960067') > 0;
where RLOS_BINARY_BP
is table name and DED_ENQ_XML
is column name (with datatype as CLOB) of Oracle database.
select *
from TABLE_NAME
where dbms_lob.instr(COLUMNNAME,'searchtext') > 0;
You can use the way like @Florin Ghita has suggested. But remember dbms_lob.substr has a limit of 4000 characters in the function For example :
dbms_lob.substr(clob_value_column,4000,1)
Otherwise you will find ORA-22835 (buffer too small)
You can also use the other sql way :
SELECT * FROM your_table WHERE clob_value_column LIKE '%your string%';
Note : There are performance problems associated with both the above ways like causing Full Table Scans, so please consider about Oracle Text Indexes as well:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/ccapp/indexing-with-oracle-text.html
Source: Stackoverflow.com