Oracle BLOB to search with like

Question

I am trying to search for text inside a BLOB for an e-mail field with an underscore at e-mail (e.g. my_email@anymailprovider.com), so at a non-BLOB it would like:

select * from TEXT_COLUMN where request_column like '%email%_%';

So, something like that to make it work:

select * from BLOB_COLUMN where dbms_lob.instr(request_column, utl_raw.cast_to_raw('%email%_%'))>0

Of course, the latter is not valid, but this is what I need to do so as to search inside the BLOB.

Related: https://stackoverflow.com/questions/2963793/oracle-blob-text-search — sticky bit, Jan 25 '19 at 13:12
Why are you storing text data in a `BLOB` that makes no sense. — , Jan 25 '19 at 14:52
@sticky bit: I've seen that post before, it did not help, so I have posted my own question. Thank you. — Kostas75, Jan 28 '19 at 12:21
@a_horse_with_no_name: How are you saying that if you don't know what information is stored exactly inside the BLOB? — Kostas75, Jan 28 '19 at 12:23
The fact that you need to use LIKE to find data in the BLOB strongly suggests that you are storing (plain) text in there, not binary data. Text is better stored in CLOB — , Jan 28 '19 at 12:25
@a_horse_with_no_name: What kind of (binary) data should be stored in a BLOB, please mention an example. Could there be any other reason to prefer to use BLOB mainly, like e.g. security reasons, even when only text is apparently stored there? — Kostas75, Jan 28 '19 at 23:55
Images, PDFs, ZIP archives, Office Documents - anything that is not text. — , Jan 29 '19 at 06:39

score 0 · Answer 1 · answered Jan 25 '19 at 14:02

0

Look for email and then look for _ following it:

SELECT *
FROM   (
  SELECT b.*,
         DBMS_LOB.INSTR(
           request_column,
           utl_raw.cast_to_raw( 'email' ),
           1,
           1
         ) AS email_location
  FROM   BLOB_COLUMN b
)
WHERE  email_location > 0
AND    DBMS_LOB.INSTR(
         request_column,
         utl_raw.cast_to_raw( '_' ),
         email_location,
         1
       ) > 0;

answered Jan 25 '19 at 14:02

MT0

143,790
11
59
117

@Kostas75 [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=47aadbbbc388bff404dbc635a31858aa) – MT0 Jan 28 '19 at 12:34

score -1 · Answer 2 · answered Jan 25 '19 at 13:41

Quoting:

"If you are storing plain text it should be a CLOB, not a BLOB, and then you can still query using LIKE. A BLOB contains binary data that Oracle doesn't know the structure of, so it cannot search it in this way."

Converting BLOB to CLOB: http://www.dba-oracle.com/t_convert_blob_to_clob_script.htm

Oracle BLOB to search with like

2 Answers2