Oracle String/Char Function - Oracle/PLSQL INSTR4 Function

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

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


The syntax for the Oracle/PLSQL INSTR4 function is:

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

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

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

start_position is optional. Defaults to 1. The first position in the string is 1. If the start_position is negative, the INSTR4 function counts start_position number from the end of string and then searches towards the beginning of string.

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

If substring is not found in string, then the INSTR4 function will return 0.


INSTR4('', 'j')