Oracle INSTR4 function.

Preamble

The Oracle/PLSQL INSTR4 function returns substring occurrence in a string using UCS4 code points.

Function Oracle/PLSQL INSTR4 syntax

INSTR4( 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 INSTR4 function calculates the position of start_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 INSTR4 function returns a numeric value. The first position in the string is 1.

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

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

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

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

--Result: 2

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

--Result: 2

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

--Result: 10

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

--Result: 12

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

--Result: 2

Tutorial: INSTR function in Oracle SQL Database. How to find position of substring

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