PLSQL | INSTR2 Function

The PLSQL INSTR2 function is used for returning the location of a substring in a string using UCS2 code points. UCS-2 codepoints is a character encoding standard in which characters are represented by a fixed-length 16 bits (2 bytes). UCS-2 represents a possible maximum of 65, 536 characters, or in hexadecimals from 0000h – FFFFh (2 bytes). 

The PLSQL INSTR2 function searches a string for a substring specified by the user using characters and returns the position in the string that is the first character of a specified occurrence of the substring. The PLSQL INSTR2 function accepts four parameters which are string, substring, start position and the nth appearance. 
The string and substring can be of any of the datatypes such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. 

Syntax:

INSTR2(string, substring [, start_position [, nth_appearance ]])

Parameters Used:

• string – It is used to specify the string in which the substring needs to be searched. It can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
• substring – It is used to specify the substring which needs to be searched. It can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
• start_position – It is an optional parameter which is used to specify the position in the string from where the search will start. The default value is 1. The INSTR2  function counts back to start_position the number of characters from the end of the string and then searches towards the beginning of string if the value inserted is negative.
• nth appearance – It is an optional parameter which is used to specify the nth appearance of substring. The default value is 1.

Supported Versions of Oracle/PLSQL:

1. Oracle 12c
2. Oracle 11g
3. Oracle 10g
4. Oracle 9i
5. Oracle 8i

Example-1: Using Character to Search Forward to Find the Position of a Substring.  

DECLARE 
   Test_String string(20) := 'Geeksforgeeks';
   
   
BEGIN 
   dbms_output.put_line(INSTR2(Test_String, 'e')); 
   
END;  

Output:

2 

Example-2: Using Character Position to Search Forward to Find the Position of a Substring.  

DECLARE 
   Test_String string(20) := 'Geeksforgeeks';
      
BEGIN 
   dbms_output.put_line(INSTR2(Test_String, 'e', 1, 1)); 
   
END;  

Output:

2 

Example-3: Using Character Position to Search Forward to Find the Position of a Substring in the 3rd position.  

DECLARE 
   Test_String string(20) := 'Geeksforgeeks';
   
BEGIN 
   dbms_output.put_line(INSTR2(Test_String, 'e', 1, 3)); 
   
END; 

Output:

10 

Example-4: Using Character Position to Search Backward to Find the Position of a Substring.  

DECLARE 
   Test_String string(20) := 'Geeksforgeeks';
   
BEGIN 
   dbms_output.put_line(INSTR2(Test_String, 'e', -2, 1)); 
   
END; 

Output:

11 

Example-5: Using a Triple-Byte Character Set to Find the Position of a Substring.  

DECLARE 
   Test_String string(20) := 'Geeksforgeeks';
   
BEGIN 
   dbms_output.put_line(INSTR2(Test_String, 'for', 1, 1)); 
   
END;  

Output:

6