Oracle INSTR2 function

Preamble

The Oracle/PLSQL INSTR2 function returns substring occurrence in a string using UCS2 code points.

Function Oracle/PLSQL INSTR2 syntax

INSTR2( string_id, substring_id [, start_position_id [, nth_appearance_id ] ] )

Parameters and function arguments

• string_id – search string. string can be CHAR, VARCHAR2, NCHAR or NVARCHAR2. string cannot be CLOB or NCLOB.
• substring_id – substring for search in string. substring can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.
• start_position_id – is the position of the character in the string from which the search starts. This argument is optional. If the argument is omitted, the default is 1. The first position in line 1. If start_position is negative, the INSTR2 function calculates the start_position position in the opposite direction from the end of the line, and then searches for the beginning of the line.
• nth_appearance_id – is nth substring occurrence. The argument is optional. If omitted, it is 1 by default.

The INSTR2 function returns a numeric value. The first position in the line is 1.
If substring is not found in the string, function INSTR2 will return 0.

INSTR2 function in the following versions of Oracle/PLSQL

Let’s consider some examples of the INSTR2 function and learn how to use the INSTR2 function in Oracle/PLSQL.

SQL> SELECT INSTR2('NaDvoreTrava', 'a') FROM DUAL;

--Result: 2

SQL> SELECT INSTR2('NaDvoreTrava', 'a', 1, 1) FROM DUAL;

--Result: 2

SQL> SELECT INSTR2('NaDvoreTrava', 'a', 1, 2) FROM DUAL;

--Result: 10

SQL> SELECT INSTR2('NaDvoreTrava', 'a', 1, 3) FROM DUAL;

--Result: 12

SQL> SELECT INSTR2('NaDvoreTrava', 'a', -3, 2) FROM DUAL;

--Result: 2

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​