FB

In this article, we’ll look at the INSTR function (including its variants), explain how to use it, and see some examples.

Purpose of the Oracle INSTR Function

The Oracle INSTR function allows you to search a string, for the occurrence of another string, and return the position of the occurrence within the string.

It’s good for finding if a string exists in another string, and also for performing additional tasks on, like other text manipulation functions or updating data.

Both the string and substring parameters can be any of the following data types:

  • CHAR
  • VARCHAR2
  • NCHAR
  • NVARCHAR2
  • CLOB
  • NCLOB

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

Get Your Free PDF: 9 Ways to Improve your Database Skills

 

Oracle INSTR Function Syntax and Parameters

The syntax for the INSTR function is:

INSTR( string, substring, [start_position], [occurrence] )

This Oracle INSTR function is actually different from other functions in that it has several different versions. Each of them vary in how they determine the position of the substring to return:

  • INSTR calculates the length using characters defined by the input character set.
  • INSTRB calculates the length using bytes.
  • INSTRC calculates the length using Unicode complete characters.
  • INSTR2 calculates the lengths using USC2 code points.
  • INSTR4 calculates the lengths using USC4 code points.

The parameters of the INSTR function and its variants are:

  • string (mandatory): This is the text string that is searched in. It is usually the larger of the two strings. If you are looking to “find X within Y”, this is the Y component.
  • substring (mandatory): This is the text string that is searched for. It is usually the smaller of the two strings. If you are looking to “find X within Y”, this is the X component.
  • start_position (optional): This is a nonzero integer, and indicates where in the string value to start the search. The default is 1, which is the start of the string. If it is negative, the function searches and counts from the end of string and goes backwards.
  • occurrence (optional): This is a positive integer, and indicates which occurrence of the substring the function should search for. The default value is 1, which means the functions searches for the first occurrence.

The search performed in this function is case-sensitive.

 

Oracle INSTR Return Value

The function returns a NUMBER value, which is the number in the location of the string where the substring is found. The numbering starts at 1, so if it is the first character, the function will return 1.

If substring is not found, the function will return a zero (0).

 

Examples of the INSTR Functions

Here are some examples of the Oracle INSTR function. I find that examples are the best way for me to learn about code, even with the explanation above.

Example 1

This example just searches for the first occurrence of the letter “e”.

SELECT INSTR(‘Melbourne, Australia’, ‘e’)

FROM DUAL;

Result:

2

 

Example 2

This example searches for the first occurrence of the letter “m” (note that it is in lower case).

SELECT INSTR(‘Melbourne, Australia’, ‘m’)

FROM DUAL;

Result:

0

 

Example 3

This example searches for an uppercase “M”.

SELECT INSTR(‘Melbourne, Australia’, ‘M’)

FROM DUAL;

Result:

1

 

Example 4

This example searches for the letter “e”, starting from position 1 and finding the second occurrence.

SELECT INSTR(‘Melbourne, Australia’, ‘e’, 1, 2)

FROM DUAL;

Result:

9

This is the “e” at the end of the word “Melbourne”.

 

Example 5

This example searches for the letter “e”, starting from position 5, and finding the first occurrence.

SELECT INSTR(‘Melbourne, Australia’, ‘e’, 5, 1)

FROM DUAL;

Result:

9

This is the “e” at the end of the word “Melbourne”.

 

Example 6

This example searches for the letter “e”, starting from position 5, and finding the second occurrence.

SELECT INSTR(‘Melbourne, Australia’, ‘e’, 5, 2)

FROM DUAL;

Result:

0

No second occurrence of “e” is found after this point.

 

Example 7

This example searches for the string “bou” within the entire string.

SELECT INSTR(‘Melbourne, Australia’, ‘bou’)

FROM DUAL;

Result:

4

 

Example 8

This example searches for the letter “a” but starts from the end of the string.

SELECT INSTR(‘Melbourne, Australia’, ‘a’, -1)

FROM DUAL;

Result:

20

The first “a” that is found is the value at the end of “Australia”.

 

Example 9

This example searches for the letter “a”, but starts from the end of the string, and finds the second occurrence.

SELECT INSTR(‘Melbourne, Australia’, ‘a’, -1, 2)

FROM DUAL;

Result:

17

This is the “a” that is found in the middle of the word “Australia”, as it is the second occurrence working backward from the string.

 

Example 10 – INSTR Variants

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
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 the INSTR function are:

  • REGEXP_INSTR – Similar to the INSTR function, but allows for regular expressions.
  • LENGTH – Returns the length of the provided string.
  • REPLACE – Replaces one string with another string in the provided value.
  • SUBSTR – Returns a part of the provided string, based on the specified position and length.

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

Get Your Free PDF: 9 Ways to Improve your Database Skills

Get Your SQL Function Cheat Sheet Now: