Select Page

In 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

## Syntax

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]] )

## Parameters

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.

``````SELECT
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;``````

Result:

 SAMPLE INSTRB_TEST INSTRC_TEST INSTR2_TEST INSTR4_TEST Ｄａｔａｂａｓｅ　Ｓｔａｒ　ｔａｂｌｅ　ｃｏｌｕｍｎ　ｒｏｗ 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!

x