Purpose
INSTR() searches a string for a specified character and returns the position of the character in the string. A multi-byte character, such as a Chinese character or a full-width character, counts as one character.
Syntax
INSTR(char1,char2[,i[,j]])
Parameters
| Parameter | Description |
|---|---|
| char1 | The string to be searched. It can be of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB data type. |
| char2 | The character to be searched for. It can be of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB data type. |
| i | The position in the string where the search begins. The default value is 1. If the value is less than 0, the function searches backward from the end of the string, but still returns the positive position of the character in the string. |
| j | The position of the j-th occurrence of char2 in the string. This parameter is optional. The default value is 1. |
Return type
The return type is NUMBER.
Examples
The following example returns the position of the second occurrence of a in OceanBase in forward search.
obclient> SELECT INSTR('OceanBase','a',1,2) "INSTR" FROM DUAL;
+-------+
| INSTR |
+-------+
| 7 |
+-------+
1 row in set