CODEWITHSUNDEEP
oracle-databaseplsql
Roland
Roland

Reputation: 7875

UTL_RAW INSTR function?

I was surprised that the Oracle raw type has a SUBSTR function but no INSTR function.

Is there a way to search for a sequence of bytes in a raw type as with the INSTR function in varchar2 types?

Upvotes: 0

Views: 627

Answers (2)

collapsar
collapsar

Reputation: 17248

Employ the INSTRB function.

Example (using the string 'hello ä there' as a utf8-encoded octet sequence) [result: 13]:

SELECT INSTRB ( utl_raw.cast_to_varchar2 ( HEXTORAW('68656c6c6F20C3A4207468657265') ), 'r' ) from dual;

Note that the octet sequence represented by the raw variable does not need to be a valid utf8 encoding.
Example ( replacing 'ä' encoding of 'C3A4' with 'C020' which is an illegal octet sequence in utf 8) [result: 13]: 

SELECT INSTRB ( utl_raw.cast_to_varchar2 ( HEXTORAW('68656c6c6F20C020207468657265') ), 'r' ) from dual;

Tested on Oracle 12c1.

Upvotes: 0

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6366

Write raw to blob and use blob instr ? 


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

Upvotes: 1

Related Questions