Purpose
You can call this function to search a string for a specified character and return the position of the character in the string. A multi-byte character, for example, 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