In Oracle, the INSTR() function searches for a substring in a given string, and returns an integer indicating the position of the first character of this substring. If the substring isn’t found, the function returns 0.

INSTR() requires at least two arguments; the string, and the substring. It also accepts an optional third and fourth arguments that allows you to specify the starting position to search, and which occurrence to search for.

INSTR() can also be thought of as a group of functions. There are five separate functions; INSTR(), INSTRB(), INSTRC(), INSTR2(), and INSTR4(). Each function calculates the length in a different way.

Syntax

The syntax goes like this:

{ INSTR
| INSTRB
| INSTRC
| INSTR2
| INSTR4
}
(string , substring [, position [, occurrence ] ])

Where string is the string to search, substring is the substring to find, position is the starting position of the substring, and occurrence is which occurrence to find.

The functions calculate lengths as follows:

Function	Calculates length using…
INSTR()	Characters as defined by the input character set, with the first character of string having position 1.
INSTRB()	Bytes
INSTRC()	Unicode complete characters
INSTR2()	UCS2 code points
INSTR4()	UCS4 code points

Example

Here’s a basic example:

SELECT INSTR('Big fat cat', 'fat')
FROM DUAL;

Result:

5

Compared with INSTRB()

This example shows how the results can differ, depending on which specific function you’re using, and the character set involved.

In this case, we compare INSTR() with INSTRB():

SELECT 
    INSTR('Böyük yağlı pişik', 'yağlı') AS INSTR,
    INSTRB('Böyük yağlı pişik', 'yağlı') AS INSTRB
FROM DUAL;

Result:

   INSTR    INSTRB 
________ _________ 
       7         9

We can see that the two functions returned two different results. This is because some characters in this string use two bytes.

The INSTR() function returns the position as defined by the input character set, whereas the INSTRB() function returns the position based in bytes.

If we return to the original string, the results are the same between the two functions:

SELECT 
    INSTR('Big fat cat', 'fat') AS INSTR,
    INSTRB('Big fat cat', 'fat') AS INSTRB
FROM DUAL;

Result:

   INSTR    INSTRB 
________ _________ 
       5         5 

That’s because this string uses just one byte per character, and so the length in bytes is the same as the number of characters.

Starting Position

Here’s an example that specifies the position for which to start the search:

SELECT INSTR('That fat cat', 'at', 8)
FROM DUAL;

Result:

11

In this case, the search starts at position 8, which is after the first two occurrences. Therefore, we get the position of the third match.

Specify Which Occurrence

Here’s an example of specifying which occurrence to find:

SELECT INSTR('That fat cat', 'at', 1, 2)
FROM DUAL;

Result:

7

In this case, we started at position 1, and then searched for the second occurrence from that starting position.

Here it is again, but this time we compare three different values for the occurrence argument:

SELECT 
    INSTR('That fat cat', 'at', 1, 1) AS "o1",
    INSTR('That fat cat', 'at', 1, 2) AS "o2",
    INSTR('That fat cat', 'at', 1, 3) AS "o3"
FROM DUAL;

Result:

   o1    o2    o3 
_____ _____ _____ 
    3     7    11

But here’s what happens if we increase the position argument:

SELECT 
    INSTR('That fat cat', 'at', 5, 1) AS "o1",
    INSTR('That fat cat', 'at', 5, 2) AS "o2",
    INSTR('That fat cat', 'at', 5, 3) AS "o3"
FROM DUAL;

Result:

   o1    o2    o3 
_____ _____ _____ 
    7    11     0 

In this case we don’t get the position of the first occurrence, because it’s located before our starting position. We also get 0 in the third column because there’s no third occurrence, based on our starting position.

Negative Position

Specifying a negative value for the position causes the starting position to be counted backwards from the end of the string, and for Oracle to search backward from that position:

SELECT INSTR('That fat cat', 'at', -3)
FROM DUAL;

Result:

7

And any occurrence that is specified is counted backward from that position:

SELECT INSTR('That fat cat', 'at', -3, 2)
FROM DUAL;

Result:

3

Null Arguments

If any (or all) of the arguments are null, the result is null:

SET NULL 'null';
SELECT 
    INSTR(null, 'f', 1, 1) AS r1,
    INSTR('Coffee', null, 1, 1) AS r2,
    INSTR('Coffee', 'f', null, 1) AS r3,
    INSTR('Coffee', 'f', 1, null) AS r4
FROM DUAL;

Result:

     R1      R2      R3      R4 
_______ _______ _______ _______ 
   null    null    null    null

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Incorrect Argument Count

Calling INSTR() without passing any arguments results in an error:

SELECT INSTR()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT INSTR()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

And passing too many arguments also results in an error:

SELECT INSTR('Big fat cat', 'at', 1, 2, 3)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT INSTR('Big fat cat', 'at', 1, 2, 3)
FROM DUAL
Error at Command Line : 1 Column : 38
Error report -
SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"
*Cause:    
*Action: