PLSQL | INSTR Function

The PLSQL INSTR function is used for returning the location of a substring in a string. The PLSQL INSTR 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 INSTR 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:

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

Parameters Used

1. 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.
2. 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.
3. 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 INSTR 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.
4. 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(INSTR(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(INSTR(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(INSTR(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(INSTR(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(INSTR(Test_String, 'for', 1, 1)); 
   
END;  

Output:

6