The INSTR function searches for a specified character in a string and returns the position of the specified character. Notice
A multibyte character, such as a Chinese character and a full-width character, is calculated as one character.
Syntax
INSTR(c1,c2[,i[,j]])
Parameters
| Parameter | Description |
|---|---|
| c1 | The string to be searched. The string type can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB. |
| c2 | The string that you want to search for. The string type can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, or CLOB. |
| i | The position where the search starts. Default value: 1. If the value is less than 0, the search starts from the opposite direction, but the function returns the left-to-right position of the searched characters. |
| j | The position of the jth occurrence. Default value: 1. |
Return type
NUMBER data type
Examples
In this example, the function needs to return the position of the second ce occurrence for instring1 when the function searches forward for ce . The function needs to return the position of the second ce occurrence for instring2 when the function searches backward for ce .
SELECT INSTR ('oceanbase pratice','ce',1,2) instring1,INSTR ('oceanbase pratice','ce',-1,2) instring2 FROM DUAL;
The result is returned. For the forward search, the second occurrence of ce lies in the sixteenth character. For the backward search, the second occurrence of ce lies in the second character.
+----------+------------+
| instring1 | instring2 |
+----------+------------+
| 16 | 2 |
+----------+------------+