Purpose
This function searches for a specified character in a string and returns the position where the character is found. Multibyte characters (such as Chinese characters and full-width punctuation) are counted as one character.
Syntax
INSTR(char1,char2[,i[,j]])
Parameters
Parameter |
Description |
|---|---|
| char1 | The string to be searched. The string can be of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB data type. |
| char2 | The string to be searched for. The string can be of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB data type. |
| i | The starting position of the search. The default value is 1. If the value is less than 0, the search starts from the end of the string, but the position of the found character is returned in the forward direction. |
| j | The position of the jth occurrence of char2. This parameter is optional. The default value is 1. |
Return type
NUMBER
Examples
Return the position of the second occurrence of a in the string OceanBase.
obclient> SELECT INSTR('OceanBase','a',1,2) "INSTR" FROM DUAL;
+-------+
| INSTR |
+-------+
| 7 |
+-------+
1 row in set