CODEWITHSUNDEEP
sqloracle-databaseplsql
Dzak
Dzak

Reputation: 213

PLSQL find ascii symbol in blob

I need to find specific ascii symbols in blob, eg. non breaking space (160). I'm stuck with this code

select *
  from tableName
 where dbms_lob.instr (blobColumn,
                   NOT SURE WHAT I SHOULD LOOK FOR,
                   1,
                   1
                    ) > 0

I've tried to pass char(160), hextoraw, but none of them worked.

Upvotes: 0

Views: 97

Answers (1)

MT0
MT0

Reputation: 168623

The documented syntax is:

DBMS_LOB.INSTR (
  lob_loc    IN   BLOB,
  pattern    IN   RAW,
  offset     IN   INTEGER := 1,
  nth        IN   INTEGER := 1)
RETURN INTEGER;

So you can use:

select *
from   tableName
where  dbms_lob.instr(blobColumn, UTL_RAW.CAST_TO_RAW(CHR(160)), 1, 1) > 0

or:

select *
from   tableName
where  dbms_lob.instr(blobColumn, HEXTORAW('A0'), 1, 1) > 0

fiddle

Upvotes: 1

Related Questions