Search code examples
sqloracle-databaseclob

check if clob contains string oracle

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

Solution

  • 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%';