INSTR

The INSTR functions search string for substring. The function returns an integer indicating the position of the character in string, or a zero (0) if does not find a match.

• INSTR calculates strings using characters as defined by the input character set.

• INSTRB uses bytes instead of characters.

• INSTRC uses Unicode complete characters.

REGEXP_INSTR provides additional options.

Return Value

NUMBER

Syntax

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

Arguments

string is the text expression to search.

substring is the text string to search for.

position is a nonzero integer indicating the character in string where the function begins the search. When position is negative, then INSTR counts and searches backward from the end of string. The default value of position is 1, which means that the function begins searching at the first character of string.

occurrence is an integer indicating which occurrence of string the function should search for. The value of occurrence must be positive. The default values of occurrence is 1, meaning the function searches for the first occurrence of substring.

Example

INSTR('CORPORATE FLOOR','OR', 3, 2) searches the string CORPORATE FLOOR beginning with the third character (R) for the second instance of the substring OR. It returns the value 14, which is the position of the second O in FLOOR.