Oracle INSTR() Function

The Oracle (PL/SQL) INSTR() function returns the location of a specified substring in a given string. If the substring is not found within the given string, this function returns 0. If the string or substring is NULL, then this function returns NULL.

Note that this function performs a case-sensitive search and the first position in the string starts with 1.

Syntax

INSTR(string, substring, position, occurrence)

Parameters

`string`	`Required.` Specify the string to search. It can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
`substring`	`Required.` Specify the substring to search for in string. It can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
`position`	`Optional.` Specify position is an nonzero integer indicating the character of string where to begin the search. If position is negative, then the function counts backward from the end of string and then searches backward from the resulting position. If omitted, it defaults to 1.
`occurrence`	`Optional.` Specify a positive integer indicating which occurrence of substring to search for. If omitted, it defaults to 1.

Return Value

Returns the location of the substring in the string.

Example 1:

The example below shows the usage of INSTR() function.

INSTR('AlphaCodingSkills.com', 'A')
Result: 1

INSTR('AlphaCodingSkills.com', 'Coding')
Result: 6

INSTR('AlphaCodingSkills.com', '.com')
Result: 18

INSTR('AlphaCodingSkills.com', 'l')
Result: 2

INSTR('AlphaCodingSkills.com', 'ABC')
Result: 0

INSTR('AA BB AA BB', 'AA')
Result: 1

INSTR('AA BB AA BB', 'AA', 1, 2)
Result: 7

INSTR('AA BB AA BB', 'AA', -1)
Result: 7

INSTR('AA BB AA BB', 'AA', -1, 2)
Result: 1

INSTR('AA BB AA BB', 'AA', 3, 1)
Result: 7

Example 2:

Consider a database table called Employee with the following records:

PhoneNumber	EmpID	Address
+1-8054098000	1	Brooklyn, New York, USA
+33-147996101	2	Grenelle, Paris, France
+31-201150319	3	Geuzenveld, Amsterdam, Netherlands
+86-1099732458	4	Yizhuangzhen, Beijing, China
+65-67234824	5	Yishun, Singapore
+81-357799072	6	Koto City, Tokyo, Japan

In the query below, the INSTR() function is used to extract the country code from the PhoneNumber column records.

SELECT Employee.*, 
SUBSTR(PhoneNumber, 1, INSTR(PhoneNumber, '-') - 1) AS CountryCode 
FROM Employee;

This will produce the result as shown below:

PhoneNumber	EmpID	Address	CountryCode
+1-8054098000	1	Brooklyn, New York, USA	+1
+33-147996101	2	Grenelle, Paris, France	+33
+31-201150319	3	Geuzenveld, Amsterdam, Netherlands	+31
+86-1099732458	4	Yizhuangzhen, Beijing, China	+86
+65-67234824	5	Yishun, Singapore	+65
+81-357799072	6	Koto City, Tokyo, Japan	+81

❮ Oracle Functions