Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle String Functions / Oracle INSTR

Oracle INSTR

The Oracle INSTR() function searches for a substring in a string and returns the position of the substring in a string.

Syntax

The followig illustrates the syntax of the Oracle INSTR() function:

INSTR(string , substring [, start_position [, occurrence]])

Arguments

The Oracle INSTR() function accepts four arguments:

string

is the string or character expression that contains the substring to be found.

 substring

is the substring to be searched

start_position

is a nonzero integer that specifies where in the string the INSTR() function begins to search. The start_position is calculated using characters as defined by input character set.

If the start_position is positive, then INSTR() function searches and counts forward from the beginning of the string. In case the start_position is negative, the INSTR() function will search and count backward from the end of the string.

The start_position is an optional parameter. The default value of the start_position is 1. It means that, by default, the INSTR() function searches from the begining of the string.

occurrence

is an positive integer that specifies which occurrence of the substring for which the INSTR() function should search. The occurence is optional and its default value is 1, meaning that the INSTR() funtion searches for the first occurrence of the substring by default.

Return value

The INSTR() function returns a positive integer that is the position of a substring within a string.

If the string does not contain the substring, the INSTR() function returns 0 (zero).

Examples

Oracle INSTR

1) Search from the start of the string

The following statement returns the  location of the first occurrence of the is substring in This is a playlist, starting from position 1 (the first character) in the string.

SELECT INSTR( 'This is a playlist', 'is' ) substring_location FROM dual;
Oracle INSTR - search forward

In this example, the INSTR() function searched for the first occurrence of the substring is from the beginning of the string This is a playlist.

2) Search for the 2nd and 3nd occurrence of a substring

The following statement returns the location of the 2nd and 3rd occurrences of the substring is in This is a playlist

SELECT INSTR( 'This is a playlist', 'is', 1, 2 ) second_occurrence, INSTR( 'This is a playlist', 'is', 1, 3 ) third_occurrence FROM dual;

Oracle INSTR - search for 2nd and 3rd occurrence

In this example, we passed the start_position as 1 and the occurrence as 2 and 3 to instruct the INSTR() function to search for the 2nd and 3rd occurrences of the substring is in the string This is a playlist.

3) Search for a substring that does not exist in a string

The following example illustrates the result when the substring are is not found in the searched string:

SELECT INSTR( 'This is a playlist', 'are' ) substring_location FROM dual;
Oracle INSTR - substring not found

4) Search backward

The following example searches the first occurrence of the substring is backward from the end of the searched string.

SELECT INSTR( 'This is a playlist', 'is',-1 ) substring_location FROM dual;
Oracle INSTR - search backward

In this tutorial, you have learned how to search and return the position of a substring in a string.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle DUMP
Next Oracle INITCAP

Oracle Functions

  • Aggregate Functions
  • Analytic Functions
  • Comparison Functions
  • Date Functions
  • String Functions

String Functions

  • ASCII
  • CHR
  • CONCAT
  • CONVERT
  • DUMP
  • INSTR
  • INITCAP
  • LENGTH
  • LOWER
  • LPAD
  • LTRIM
  • REPLACE
  • REGEXP_COUNT
  • REGEXP_INSTR
  • REGEXP_LIKE
  • REGEXP_REPLACE
  • REGEXP_SUBSTR
  • RPAD
  • RTRIM
  • SOUNDEX
  • SUBSTR
  • TRANSLATE
  • TRIM
  • UPPER

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.