Term: INSTR

Definition:
The Oracle INSTR function searches inside a string for a substring. The Oracle INSTR function works in a way similar to the SUBSTR function, but INSTR returns an integer number indicating the position of the substring within the string, rather than returning the substring itself.
Note: For more complex string handling functions, you can make use of Regular Expressions by using the extended version of the INSTR function named REGEXPR_INSTR.

Example Syntax:

INSTR( source_string, substring [, start_position [, occurrance ] ] )

source_string is the string to be searched.

substring is the character string to be searched for inside of source_string.

start_position is an optional argument. It is an integer value that tells Oracle where to start searching in the source_string. If the start_position is negative, then Oracle counts back that number of characters from the end of the source_string and then searches backwards from that position. If omitted, this defaults to 1.

occurrence is an integer indicating which occurrence of substring Oracle should search for. That is, should INSTR return the first matching substring, the second matching substring, etc. This argument is optional. If omitted, it defaults to 1.

If the substring is not found in source_string, the Oracle INSTR function will return 0.

Notes:

Both source_string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The value returned is of NUMBER datatype.
Both start_position and occurrence must be an integer of datatype NUMBER, or any datatype that can be implicitly converted to NUMBER.
The first position in the source_string is 1, not 0.
The INSTR search is case sensitive.

Example Usage:
Using Oracle Instr

INSTR('Welcome to PSOUG.org', 'o')        would return 5   (Finds the first occurrence of 'o')

INSTR('Welcome to PSOUG.org', 'o', 1, 1)  would return 5.  (Finds the first occurrence of 'o')

INSTR('Welcome to PSOUG.org', 'o', 1, 2)  would return 10. (Finds the second occurrence of 'o')

INSTR('Welcome to PSOUG.org', 'o', 1, 3)  would return 18. (Finds the third occurrence of 'o')

INSTR('Welcome to PSOUG.org', 'o', -2, 2) would return 10. (Count back 2, then find the 2nd 'o')