Oracle INSTR() Function

Oracle INSTR() is a built-in function that searches for a substring in a given string and returns an integer that represents the index position of the string.

Oracle INSTR() performs a case-sensitive search.

Oracle has several similar functions, but they calculate position in different ways:

• INSTR uses character-defined positions as defined by the input character set, with the position of the first character being 1.
• INSTRB uses byte rather than character-defined positions.
• INSTRC uses Unicode complete character-defined positions.
• INSTR2 uses UCS2 code point-defined positions.
• INSTR4 uses UCS4 code point-defined positions.

If you need to match based on a regular expression pattern, use REGEXP_INSTR().

Oracle INSTR() Syntax

Here is the syntax for the Oracle INSTR() function:

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

Parameters

string

Required. The string to search within. It can be of type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

substring

Required. The substring to search for. It can be of type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

position

Optional. It is a nonzero integer that indicates the starting index position for the search. If position is negative, Oracle counts backward from the end of the string, then searches forward from the resulting position. It starts at 1.

occurrence

Optional. It is an integer that indicates which occurrence of the search string to search for.

Return Value

The Oracle INSTR() function returns an integer that is the index position of the substring substring within the string string.

If the given substring is not found in the given string, INSTR() returns 0.

If any argument is NULL, INSTR() returns NULL.

Oracle INSTR() Examples

Here are several examples demonstrating the usage of the Oracle INSTR() function.

Basic Usage

The following statement searches for l in Hello World:

SELECT
    INSTR('Hello World', 'l') Result
FROM dual;

Output:

   RESULT
_________
        3

In this example, l is the 3rd character in Hello World, so it returns 3.

Starting Position

The Oracle INSTR() function allows you to specify the starting position for the search:

SELECT
    INSTR('Hello World', 'l', 4) Result
FROM dual;

Output:

   RESULT
_________
        4

In this example, we indicate to start the search from the 4th character using the position parameter.

Nth Occurrence

The Oracle INSTR() function allows you to specify which occurrence of the substring to search for:

SELECT
    INSTR('Hello World', 'l', 1，1) Result1,
    INSTR('Hello World', 'l', 1，2) Result2,
    INSTR('Hello World', 'l', 1，3) Result3
FROM dual;

Output:

   RESULT1    RESULT2    RESULT3
__________ __________ __________
         3          4         10

Not Found

If the given substring cannot be found in the given string, INSTR() will return 0.

SELECT
    INSTR('Hello World', 'x') Result1,
    INSTR('Hello World', 'l', 20) Result2
FROM dual;

Output:

   RESULT1    RESULT2
__________ __________
         0          0

In this example, although l is contained in Hello World, INSTR('Hello World', 'l', 20) returns 0 because the starting position is 20.

NULL Parameters

If any parameter is NULL, the function will return NULL.

SET NULL 'NULL';
SELECT
    INSTR(NULL, 'A') null1,
    INSTR('A', NULL) null2,
    INSTR('A', 'A', NULL) null3,
    INSTR('A', 'A', 1, NULL) null4
FROM dual;

Output:

   NULL1    NULL2    NULL3    NULL4
________ ________ ________ ________
    NULL     NULL     NULL     NULL

In this example, we use the SET NULL 'NULL'; statement to display NULL values as the string 'NULL'.

Conclusion

The Oracle INSTR() is a built-in function that searches for a substring in a given string and returns an integer representing the index position of the string.