SQL Tutorial SQL Advanced SQL Database SQL References

Oracle INSTR4() Function



The Oracle (PL/SQL) INSTR4() function returns the location of a specified substring in a given string, using UCS4 code points. 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

INSTR4(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 INSTR4() function.

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

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

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

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

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

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

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

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

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

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

Example 2:

Consider a database table called Employee with the following records:

PhoneNumberEmpIDAddress
+1-80540980001Brooklyn, New York, USA
+33-1479961012Grenelle, Paris, France
+31-2011503193Geuzenveld, Amsterdam, Netherlands
+86-10997324584Yizhuangzhen, Beijing, China
+65-672348245Yishun, Singapore
+81-3577990726Koto City, Tokyo, Japan

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

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

This will produce the result as shown below:

PhoneNumberEmpIDAddressCountryCode
+1-80540980001Brooklyn, New York, USA+1
+33-1479961012Grenelle, Paris, France+33
+31-2011503193Geuzenveld, Amsterdam, Netherlands+31
+86-10997324584Yizhuangzhen, Beijing, China+86
+65-672348245Yishun, Singapore+65
+81-3577990726Koto City, Tokyo, Japan+81

❮ Oracle Functions