Oracle PL/SQL - INSTRB function

In this tutorial we will explain how to use the Oracle PL/SQL INSTRB  function with syntax and examples

INSTRB Function : 

Syntax : INSTRB ('input_string',  'search_string',  start_position,  occurrence);

• In Oracle PL/SQL INSTRB function returns the position of the "search_string" in the given input_string using bytes instead of characters.
• input_string  and search string can be of data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB
• return type is of data type NUMBER.
• The first position in the input_string is treated as 1.
• start_position is the position in the input_string from where the search will start.
• If start_position is negative, then Oracle counts backward from the end of string and then searches backward from the resulting position.
• occurrence must be a positive integer.
• If search_string is not found in input_string, then the INSTRB function will return 0.
• start_position and occurrence are optional and the default value is 1.

EXAMPLE 1 :

SELECT INSTRB('DEVELOPERS ARENA','E',3, 2)
  "INSTRING_POSITION" FROM DUAL;
 
  INSTRING_POSITION
-------------------
                  8

EXAMPLE 2 :

SELECT INSTRB('DEVELOPERS ARENA','E',-2, 3)
  "INSTRING_POSITION" FROM DUAL;
 
  INSTRING_POSITION
-------------------
                  4