Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Conditions (like "like") on binary field (blob) in oracle

How can I search in (put condition on) blob field in oracle, like text fields?

I need someting like:

select * from table_name where blob_field like '%00ff00ff%'

Oracle throws some error on it.

like image 933
Polygon Avatar asked Jan 02 '11 10:01

Polygon


People also ask

What is data type BLOB in Oracle?

A BLOB (binary large object) is a varying-length binary string that can be up to 2,147,483,647 characters long. Like other binary types, BLOB strings are not associated with a code page. In addition, BLOB strings do not hold character data.

How are BLOBs stored in Oracle?

BLOBs are not stored in the normal database files on disk in the same way as is other data managed by DB. Instead, they are stored as binary files in a special directory set aside for the purpose.

What is Dbms_lob Instr?

INSTR Function. This function returns the matching position of the nth occurrence of the pattern in the LOB , starting from the offset you specify.

How do I search for a specific word in Oracle?

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.


1 Answers

You can use dbms_lob.instr for this purpose i.e.

   select * from table_name 
   where dbms_lob.instr(blob_field, utl_raw.CAST_TO_RAW('00ff00ff'), 1, 1) > 0
like image 135
Michael Pakhantsov Avatar answered Oct 25 '22 13:10

Michael Pakhantsov