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.

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

Answer 2

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