CASE INSENSITIVE SEARCH LOB FIELD

[rhyswilliams]

can anyone suggest how i can carry out a case insensitive search on a CLOB field in Oracle.
NB
i need to be able to search the entire field in one go each time.
TIA
Rhys

 

[carp]

I've run into a similar situation and solved it by writing my own function:

CREATE OR REPLACE FUNCTION upper_clob(p_clob CLOB) RETURN CLOB
AS
v_clob CLOB; -- TEMPORARY CLOB TO WRITE TO
v_posn NUMBER := 1; -- CURRENT POSITION WITHIN CLOB
v_holder VARCHAR2(4000); -- CHARACTER STRING HOLDER

BEGIN
dbms_lob.createtemporary(v_clob,TRUE,dbms_lob.CALL);
WHILE (v_posn < dbms_lob.getlength(p_clob)) LOOP
v_holder := dbms_lob.substr(p_clob,4000,v_posn);
v_posn := v_posn + 4000;
v_holder := upper(v_holder);
dbms_lob.write(v_clob, length(v_holder),v_posn,v_holder);
END LOOP;
return v_clob;
end;

Now if I want to search for a string in a clob, but it has to be case-insensitive, I can do something like:

SQL> select count(*) from my_table
where dbms_lob.instr(upper_lob(my_clob_column),'TARGET_STRING') > 0;

 

[rhyswilliams]

i'll give this a go, thanks