INSTR v18

The INSTR function returns the location of the nth occurrence of a given pattern in a large object.

For BFILEs, the file must already be opened using a FILEOPEN operation.

<position> NUMBER INSTR(<lob_loc> { BLOB | CLOB },
   <pattern> { RAW | VARCHAR2 } [, <offset> NUMBER [, <nth> NUMBER ]])

<position> NUMBER INSTR(<file_loc> IN { BFILE }, 
   <pattern> IN { RAW }, <offset> IN NUMBER, <nth> IN NUMBER)

Parameters

lob_loc

Locator of the large object in which to search for pattern.

file_loc

File locator for the BFILE in which to search for pattern.

pattern

Pattern of bytes or characters to match against the large object or BFILE. If lob_loc is a BLOB or file_loc is a BFILE, pattern must be RAW. If lob_loc is a CLOB, pattern must be VARCHAR2.

offset

Position in lob_loc or BFILE to start search for pattern. The first byte/character is position 1. The default is 1.

nth

Search for pattern, nth number of times starting at the position given by offset. The default is 1.

position

Position in the large object where pattern appears the nth time, starting from the position given by offset.

Example

DECLARE
   src_loc        BFILE := BFILENAME('ANOTHER_DIR', 'a.txt');
   src_offset     NUMBER := 1;
   nth            NUMBER := 1;
   position       NUMBER;
BEGIN
  /* Opening the source BFILE is mandatory */
  DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);

   /* Initialize the pattern for which to search, find the first occurrence of
    the pattern starting from the beginning of the BFILE: */
   position := DBMS_LOB.INSTR(src_loc, utl_raw.cast_to_raw('t'), src_offset, nth);
   DBMS_OUTPUT.PUT_LINE('Position of pattern in BFILE is - ' || position );

   /* Close the BFILE: */
   DBMS_LOB.FILECLOSE(src_loc);
END;