The INSTR function returns the position of the nth occurrence of a match in a LOB starting from the specified offset.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
Syntax
DBMS_LOB.INSTR (
lob_loc IN BLOB,
pattern IN RAW,
offset IN INTEGER := 1,
nth IN INTEGER := 1)
RETURN INTEGER;
DBMS_LOB.INSTR (
lob_loc IN CLOB CHARACTER SET ANY_CS,
pattern IN VARCHAR2 CHARACTER SET ANY_CS,
offset IN INTEGER := 1,
nth IN INTEGER := 1)
RETURN INTEGER;
Parameters
| Parameter | Description |
|---|---|
| lob_loc | The locator of the LOB to be checked. For more information, see Use DBMS_LOB. |
| pattern | The pattern to be tested. The pattern is a set of RAW bytes for a BLOB and a string (VARCHAR) for a CLOB. The maximum length of the pattern is 16383 bytes. |
| offset | The absolute offset (origin 1) of the byte (BLOB) or character (CLOB) at which the pattern matching starts. |
| nth | The occurrence number, starting from 1. |
Return value
| Return value | Description |
|---|---|
| INTEGER | The offset of the start of the match, in bytes or characters. If no match is found, 0 is returned. |
| NULL | NULL is returned in any of the following cases:
|
Considerations
The VARCHAR buffer (pattern parameter) must match the form of the CLOB parameter. If the input LOB parameter is of type CLOB, the buffer must contain CHAR data.
Operations that accept RAW or VARCHAR parameters for pattern matching (such as INSTR) do not support regular expressions or special matching characters in the pattern parameter or substring, such as SQL LIKE.