Oracle INSTR function
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
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises