Oracle INSTRB, INSTRC, INSTR2, and INSTR4 Function with ExamplesIn this article, I’ll explain what all of the variations of the INSTR function (INSTRB, INSTRC, INSTR2 and INSTR4) do, and show you some examples

Purpose of the Oracle INSTR2, INSTR4, INSTRB, and INSTRC Functions

These INSTR functions are variations of the basic INSTR function, which searches inside a string for a substring, and returns the position in the string of the first occurrence of the substring.

Where these variations differ is how they determine the position of the substring to return:

  • INSTRB calculates lengths using bytes
  • INSTRC calculates lengths using Unicode characters
  • INSTR2 calculates lengths using USC2 code points
  • INSTR4 calculates lengths using USC4 code points



The syntax of these functions are very similar:

INSTRB ( string, substring [,position [,occurrence]] )

INSTRC ( string, substring [,position [,occurrence]] )

INSTR2 ( string, substring [,position [,occurrence]] )

INSTR4 ( string, substring [,position [,occurrence]] )



The parameters of the INSTRB, INSTRC, INSTR2 and INSTR4 functions are:

  • string (mandatory): This is the string to search in. It’s usually the larger of the two strings, and it usually contains the substring.
  • substring (mandatory): This is the string to search for. It’s usually the smaller of the two strings.
  • position (optional): An integer value that indicates which position within the string to begin the search. This parameter is where these four functions are different from each other.
  • occurrence (optional): The occurrence of the substring that is searched for.


Examples of the INSTRB, INSTRC, INSTR2, and INSTR3 Functions

Here are some examples of the INSTR variation functions.

This example shows how each of the INSTR functions treat a specific string. I’ve used the TO_MULTI_BYTE function to show differences with some of the INSTR functions.

TO_MULTI_BYTE('Database Star table column row') AS sample,
INSTRB(TO_MULTI_BYTE('Database Star table column row'), TO_MULTI_BYTE('s'), 1) AS instrb_test,
INSTRC(TO_MULTI_BYTE('Database Star table column row'), TO_MULTI_BYTE('s'), 1) AS instrc_test,
INSTR2(TO_MULTI_BYTE('Database Star table column row'), TO_MULTI_BYTE('s'), 1) AS instr2_test,
INSTR4(TO_MULTI_BYTE('Database Star table column row'), TO_MULTI_BYTE('s'), 1) AS instr4_test
FROM dual;


Database Star table column row 19 7 7 7

You can see that some of the values are different.


Similar Functions

Some functions which are similar to these functions are:

  • INSTR: The basic INSTR function searches for a string within another string.
  • SUBSTR: Returns a smaller string from a larger string.
  • LENGTH2/LENGTH4/LENGTHB/LENGTHC: Returns the length of a string in a few different methods.

If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Improve Your Oracle SQL With My 10-Day Email Course