Menu
Is it possible to search through blob text using sql statement? I can do select * from $table where f1 like '%foo%' if the f1 is varchar, how about f1 is a blob? Any counter part for this?
948
To enter an Oracle Text query, use the SQL SELECT statement. Depending on the type of index, you use either the CONTAINS or CATSEARCH operator in the WHERE clause. You can use these operators programatically wherever you can use the SELECT statement, such as in PL/SQL cursors.
INSTR Function. This function returns the matching position of the nth occurrence of the pattern in the LOB , starting from the offset you specify.
When calling DBMS_LOB . SUBSTR from the client (for example, in a BEGIN / END block from within SQL*Plus), the returned buffer contains data in the client's character set. Oracle converts the LOB value from the server's character set to the client's character set before it returns the buffer to the user.
This is quite possible and easy to do.
Simply use dbms_lob.instr in conjunction with utl_raw.cast_to_raw
So in your case, if t1 is a BLOB the select would look like:
select * from table1 where dbms_lob.instr (t1, -- the blob utl_raw.cast_to_raw ('foo'), -- the search string cast to raw 1, -- where to start. i.e. offset 1 -- Which occurrance i.e. 1=first ) > 0 -- location of occurrence. Here I don't care. Just find any ; If it is a Word or PDF document, look into Oracle Text.
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
