Oracle String/Char Function - Oracle/PLSQL INSTR2 Function

This Oracle tutorial explains how to use the Oracle/PLSQL INSTR2 function.

The Oracle/PLSQL INSTR2 function returns the location of a substring in a string using UCS2 code points.


The syntax for the Oracle/PLSQL INSTR2 function is:

INSTR2( string, substring [, start_position [,  nth_appearance ] ] )

string is the string to search. It can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2. string can not be CLOB or NCLOB.

substring is the substring to search for. substring can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

start_position is optional. It defaults to 1. The first position in the string is 1. If the start_position is negative, INSTR2 counts start_position from the end of string and then searches towards the beginning of string.

nth_appearance is the nth appearance of substring. This is optional. It defaults to 1.

If substring is not found in string, INSTR2 returns 0.


INSTR2('', 'j')