All Products
Search
Document Center

INSTR

Last Updated: Jun 18, 2021

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

 

+----------+
| instring |
+----------+
|        3 |
+----------+

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 |
+----------+------------+