Oracle INSTRB function

Preamble

The Oracle/PLSQL INSTRB function returns substring occurrence in a string, bytes instead of characters.

Syntax of the Oracle/PLSQL INSTRB function

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

Parameters and function arguments

• string_id – search string. string can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.
• substring_id – substring to search the 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 INSTRB 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 INSTRB function returns a numeric value. The first position in the line is 1.
If substring is not found in the string, the INSTRB function will return 0.

INSTRB function in the following versions of Oracle/PLSQL

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

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

--Result: 2

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

--Result: 2

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

--Result: 10

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

--Result: 12

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

--Result: 2

Oracle INSTR Function

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