INSTR function in Oracle

INSTR is one of the vital string/char functions of Oracle. It is used to get the location of a substring, where a substring is a part of a string. The INSTR function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i and Oracle 8i.

Syntax:

INSTR( string, substring, start_position, nth_appearance )

Parameters:
string: It is used to specify the string to set.
substring: It is used to specify the substring to search for.
start_position: It is an optional parameter which is used to specify the position in string where the search will start. Its default value is 1, i.e, the first position. It also accepts negative value, and in that case it counts back from the end of string and then starts the search backwards towards the beginning of the string. However, whatever be the case the value of the position will be same as counted from the start, i.e, the first position belongs to the first character of the string only the start position is from the beginning or the end.
nth_appearance: It is also an optional parameter which is used to specify the nth appearance of the substring. Its default value is 1.

Example 1:

INSTR (‘HELLO WORLD’, L)

Output:

3

Explanation:
The first occurence of ‘L’ from the beginning of the given string is at the third position.

Example 2:

INSTR (‘HELLO WORLD’, L, 1, 2)

Output:

4

Explanation:
The second occurence of ‘L’ from the beginning of the given string is at the fourth position.

Example 3:

INSTR (‘HELLO WORLD’, L, -1, 1)

Output:

10

Explanation:
The first occurence of ‘L’ from the end of the given string is at the tenth position.

Example 4:

INSTR (‘HELLO WORLD’, L, -1, 3)

Output:

3

Explanation:
The third occurence of ‘L’ from the end of the given string is at the third position.

Please Share