The INSTR function returns the matching location of the nth occurrence of a specified pattern in the LOB, starting from a specified offset position.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL-compatible mode.
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 check. For more information, see Considerations. |
| pattern | The pattern to look for. For a BLOB, the pattern is a group of RAW bytes; for a CLOB, the pattern is a string of the VARCHAR type. The pattern can be up to 16,383 bytes in size. |
| offset | The absolute offset from the beginning of the LOB, with 1 as the origin, for the pattern matching operation. The value is in bytes for BLOBs and in characters for CLOBs. |
| nth | The occurrence number to look for. This value starts at 1. |
Return values
Return value |
Description |
|---|---|
| INTEGER | The offset, in bytes or characters, at which the nth matched pattern starts. If the pattern is not found, 0 is returned. |
| NULL | If the value is NULL, possible reasons are as follows:
|
Considerations
The form of the VARCHAR buffer, that is, the form of the pattern parameter, must match the form of the CLOB parameter. If the type of the input LOB parameter is CLOB, the data in the buffer must be of the CHAR type.
The RAW or VARCHAR parameter can be used for pattern matching operations such as INSTR. Regular expressions or special matching strings such as LIKE in SQL are not supported in pattern parameters or strings.