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

Question

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.

Blancheblanchette

Dup of https://mcmap.net/q/439768/-oracle-blob-text-search – Angers

Answer 1

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

Lurid

The pattern for searching BLOBs has to be a RAW datatype. Oh, and I corrected the package name in the sample too ;) – Laverne

Thank you, that was very helpful (came across this a year after you answered!) – Galvanize

Answer 2

select *
from NDF_MODEL_PARAM
where dbms_lob.instr(VALUE, 'NaN') > 0;

Seersucker