Oracle INSTR function

Last update on August 19 2022 21:50:41 (UTC/GMT +8 hours)

Description

The Oracle INSTR function is used to search string for substring and find the location of the substring in the string. If a substring that is equal to substring is found, then the function returns an integer indicating the position of the first character of this substring. If no such substring is found, then the function returns zero.

Syntax:

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

INSTRB uses bytes instead of characters. INSTRC uses Unicode complete characters. INSTR2 uses UCS2 code points. INSTR4 uses UCS4 code points.

Parameters:

Name	Description	Data Types
string	The string to search.	CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB, exceptions are INSTRC, INSTR2, and INSTR4, which do not allow string to be a CLOB or NCLOB.
substring	The substring to search for in string.	CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
position	Optional. The position is an nonzero integer indicating the character of string from where the search begins. If omitted, it defaults to 1. The first position in the string is 1. If position is negative, then INSTR function counts backward from the end of string and then searches backward from the resulting position.	NUMBER, or any data type that can be implicitly converted to NUMBER and must resolve to an integer.
occurrence	Optional, The occurrence is an integer indicating which occurrence of substring in string INSTR function should search for.	NUMBER, or any data type that can be implicitly converted to NUMBER and must resolve to an integer.

Return Value Type

NUMBER

Applies to

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Examples: Oracle INSTR function

The following examples explore how the INSTR function works.

SELECT INSTR('THIS IS THE THING','TH') "Position Found"
FROM DUAL;

Sample Output:

Position Found
--------------
             1

Here is another example.

SELECT INSTR('THIS IS THE THING','TH',1,1) "Position Found"
FROM DUAL;

Sample Output:

Position Found
--------------
             1

Here is another example.

SELECT INSTR('THIS IS THE THING','TH',1,2) "Position Found"
FROM DUAL;

Sample Output:

Position Found
--------------
             9

Here is another example.

SELECT INSTR('THIS IS THE THING','TH',-3,3) "Position Found"
FROM DUAL;

Sample Output:

Position Found
--------------
             1

Previous: ASCII
Next: LENGTH