Oracle INSTR function.

Preamble

The Oracle/PLSQL INSTR function returns the n-th occurrence of a substring in a string.

Oracle/PLSQL INSTR function syntax

INSTR( string1, substring1 [, start_position_id [, nth_appearance_id ] ] )

Parameters and function arguments

• string1 – is a search string. A string can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.
• substring1 – is a 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 will start. This argument is optional. If the argument is omitted, the default is 1. The first position in line 1. If the start_position parameter is negative, the INSTR 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 the nth occurrence of the substring. The argument is optional. If omitted, it is 1 by default.

If a substring is not found in a string, the INSTR function will return 0.

The INSTR function can be used in the following versions of Oracle/PLSQL

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

SQL> SELECT INSTR('Grass in the Yard', 'a') FROM DUAL;
--Result: 2

SQL> SELECT INSTR('Grass Yard', 'a', 1, 1) FROM DUAL;
--Result: 2

SQL> SELECT INSTR('Grass Outdoors', 'a', 1, 2) FROM DUAL;
--Result: 12

SQL> SELECT INSTR('Grass on the Yard', 'a', 1, 3) FROM DUAL;
--Result: 14

SQL> SELECT INSTR('Grass on the Yard', 'a', -3, 2) FROM DUAL;
--Result: 2

The Oracle INSTR function allows you to search a string for the occurrence of another string

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.​