The INSTR function returns the location of the nth
occurrence of a given pattern within a large object.
The function returns an INTEGER value of the position
within the large object where the pattern appears for the nth time,
as specified by nth. This value starts from the
position given by offset.
Syntax
>>-DBMS_LOB.INSTR--(--lob_loc--,--pattern----------------------->
>--+-----------------------+--)--------------------------------><
'-,--offset--+--------+-'
'-,--nth-'
Parameters
- lob_loc
- An input argument of type BLOB or CLOB that specifies the large
object locator of the large object in which to search for the pattern.
- pattern
- An input argument of type BLOB(32767) or VARCHAR(32672) that specifies
the pattern of bytes or characters to match against the large object.
Note that pattern must be BLOB if lob_loc is
a BLOB; and pattern must be VARCHAR if lob_loc is
a CLOB.
- offset
- An optional input argument of type INTEGER that specifies the
position within lob_loc to start searching for
the pattern. The first byte or character is at
position 1. The default value is 1.
- nth
- An optional argument of type INTEGER that specifies the number
of times to search for the pattern, starting at
the position given by offset. The default value
is 1.
Authorization
EXECUTE privilege on the DBMS_LOB
module.
DB2 10.5 for Linux, UNIX, and Windows