Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to serch a character string in a BLOB (case insensitive)

400782Aug 17 2004 — edited Aug 17 2004
Hi
Could somebody help me with this please.
I need to search for the existance of a character string in a BLOB column of a table.

I can do it in the below way.

PROCEDURE search (in_search VARCHAR2) IS
lob_doc BLOB;
Pattern VARCHAR2(30);
Position INTEGER := 0;
Offset INTEGER := 1;
Occurrence INTEGER := 1;
BEGIN
Pattern := utl_raw.cast_to_raw(in_search);
SELECT BLOB_CONTENT INTO lob_doc
FROM documents WHERE ein = '702265981';
DBMS_LOB.OPEN (lob_doc, DBMS_LOB.LOB_READONLY);
Position := DBMS_LOB.INSTR(lob_doc, Pattern, Offset, Occurrence);
IF Position = 0 THEN
DBMS_OUTPUT.PUT_LINE('Pattern not found');
ELSE
DBMS_OUTPUT.PUT_LINE('The pattern occurs at '|| position);
END IF;
DBMS_LOB.CLOSE (lob_doc);
END search;



But the problem is, it does a case sensitive search.
I need a case-insensitive search

Thanks in Advance
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 14 2004
Added on Aug 17 2004
1 comment
1,429 views