19

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.

Polygon
  • 191
  • 1
  • 1
  • 3

2 Answers2

30

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
APC
  • 144,005
  • 19
  • 170
  • 281
Michael Pakhantsov
  • 24,855
  • 6
  • 60
  • 59
  • 3
    The pattern for searching BLOBs has to be a RAW datatype. Oh, and I corrected the package name in the sample too ;) – APC Jan 02 '11 at 12:26
  • Thank you, that was very helpful (came across this a year after you answered!) – Philip Tenn Oct 03 '12 at 20:03
0
select *
from NDF_MODEL_PARAM
where dbms_lob.instr(VALUE, 'NaN') > 0;
Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
Bhushan Phalak
  • 73
  • 7