check if clob contains string oracle

Master Yi picture Master Yi · May 17, 2017 · Viewed 29.4k times · Source

currently i have query with this code to_char(CLOB_COLUM) like %s but the following wont work for very big clob. Is there another solution to check if this column contains some string. Using oracle 11.2.0.4.0

Answer

MT0 picture MT0 · May 17, 2017

You can use DBMS_LOB.INSTR( clob_value, pattern [, offset [, occurrence]] ):

SELECT *
FROM   your_table
WHERE  DBMS_LOB.INSTR( clob_column, 'string to match' ) > 0;

or

SELECT *
FROM   your_table
WHERE  clob_column LIKE '%string to match%';