Forum OpenACS Q&A: Response to case insensitive search with CLOB

For a quick hack, you can create an additional column that is the lower case of the clob, and then use instr on the lower case version

create table keyword_test (
  id              integer primary key,
  contents        clob,
  contents_lower  clob
);


insert into keyword_test (id,contents) values (1, 'This is string ONE');
insert into keyword_test (id,contents) values (2, 'This is string TWO');
commit;

update keyword_test set contents_lower=lower(contents);
commit;

SQL> select id, dbms_lob.instr(contents_lower, 'one') 
       from keyword_test;

	ID DBMS_LOB.INSTR(CONTENTS_LOWER,'ONE')
---------- ------------------------------------
	 1				     16
	 2				      0

You can also use interMedia to provide keyword searching on the clob