Oracle INSTR Function: A Simple Guide to Finding Substrings

Ever needed to find the exact position of a word or letter within a string in Oracle SQL? The INSTR function is the perfect tool for the job.

This simple guide will explain what the Oracle INSTR function is, its syntax, and how to use it with clear, practical examples.

What is the INSTR Function in Oracle?

The INSTR function (which stands for "in string") searches a main string for a specific substring. When it finds the substring, it returns a number (NUMBER) that tells you the starting position of that substring.

If the substring is not found inside the main string, the function returns 0.

This is extremely useful for finding data, validating string formats, or for use with other functions (like SUBSTR) to extract data from a string.

INSTR Function Syntax

The syntax for the INSTR function has both a simple and a more advanced form:

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

Let's break down each part:

• string: This is the main string you want to search inside (e.g., 'Hello World').
• substring: This is the smaller string you are looking for (e.g., 'World').
• [start_position] (Optional): This is the numeric position in the string where you want to begin the search.
  • If you don't provide it, it defaults to 1 (the very beginning of the string).
  • If you use a negative number, it will start from the end of the string and search backward.
• [occurrence] (Optional): This is an integer for which time you want to find the substring.
  • If you don't provide it, it defaults to 1 (it will find the first match).
  • If you set it to 2, it will skip the first match and find the second one.

Oracle INSTR Function Examples

Here are two practical examples of how to use INSTR in your Oracle SQL queries.

Example 1: Basic Substring Find with INSTR

This is the simplest use of INSTR. We want to find the starting position of the word 'World' in the string 'Hello World'.

Remember, the position count starts at 1.

• H is 1
• e is 2
• ...
• (space) is 6
• W is 7

We use the DUAL table for this test, which is a special one-row, one-column table in Oracle.

Query:

SELECT INSTR('Hello World', 'World') AS "Position"
FROM DUAL;

Result:

  Position
----------
         7

The function correctly returns 7, as that is the position where 'World' begins.

Example 2: Finding the "Second" Occurrence of a Substring with INSTR

This is a more advanced example that uses the optional [occurrence] parameter. Imagine you want to find the position of the second 'i' in the word 'Mississippi'.

• The first 'i' is at position 2.
• The second 'i' is at position 5.

Query:

-- We will search 'Mississippi'
-- for the substring 'i'
-- start at position 1 (default)
-- and find the 2nd occurrence
SELECT INSTR('Mississippi', 'i', 1, 2) AS "Second_i_Position"
FROM DUAL;

Result:

Second_i_Position
-----------------
                5

This query skips the first 'i' (at position 2) and finds the second one, which starts at position 5. If we had asked for the 5th occurrence (INSTR('Mississippi', 'i', 1, 5)), it would return 0 because there is no 5th 'i'.