Oracle INSTR function

Learn how to find the position of a sub string from the main string with oracle INSTR function with example queries.

INSTR –tells the position of sub-string from the main string.

IN refers to Index of sub string

STR refers the Main string

INSTR() is a function that search for the position of substring in the main string and returns the position value of substring.

Below is the syntax of the Oracle INSTR() function

Syntax

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

string – is the string or character expression that contains the sub-string to be found.

sub-string – is the string to be searched in the main string.

start_position- specifies the position that sub-string to be searched from the main string.

value of start_position can be either negative or positive but not zero (a non zero integer value).

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 beginning of the string.

Positive value indicates that search from left(begin) to right(end).
Negative value indicates that search from right(end) to left(begin).

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

Example queries for finding the position of a sub-string

INPUT STRING:
[email protected]

QUERY 1:

find the index of first occurrence and second of occurrence of “.” from the input string.

WITH CTE(EMAIL_ID) AS
 (SELECT '[email protected]' AS EMAIL_ID 
  FROM DUAL
 )

SELECT INSTR(EMAIL_ID, '.', 1, 1) firstDOT_POSITION,
       INSTR(EMAIL_ID, '.', 1, 2) SecondDot_Position
FROM CTE;

FIRSTDOT_POSITION	SECONDDOT_POSITION
10	18

QUERY 2:

find the index of @ for the give input string.

WITH CTE(EMAIL_ID) AS
  (SELECT '[email protected]' AS EMAIL_ID 
   FROM DUAL
  )
 SELECT INSTR(EMAIL_ID, '@', 1, 1) firstAT      
 FROM CTE;

FIRSTAT

20

QUERY 3:

find the index last occurrence of “.” for the give input string.

WITH CTE(EMAIL_ID) AS
  (SELECT '[email protected]' AS EMAIL_ID 
   FROM DUAL
  )
 SELECT INSTR(EMAIL_ID, '.', -1, 1) LASTDOT_POSITION      
 FROM CTE;

LASTDOT_POSITION

26

Below 3 queries gives same result when you run.

QUERY 1: WITHOUT start_position,occurrence

WITH CTE(EMAIL_ID) AS
 (SELECT '[email protected]' AS EMAIL_ID 
  FROM DUAL
  )
SELECT INSTR(EMAIL_ID, '.') firstDOT_POSITION     
FROM CTE;

//IS SIMILAR TO

QUERY 2: only with start_position

WITH CTE(EMAIL_ID) AS
 (SELECT '[email protected]' AS EMAIL_ID 
  FROM DUAL
  )
SELECT INSTR(EMAIL_ID, '.',1) firstDOT_POSITION     
FROM CTE;

//IS SIMILAR TO

QUERY 3: both with start_position and occurrence

WITH CTE(EMAIL_ID) AS
 (SELECT '[email protected]' AS EMAIL_ID 
  FROM DUAL
  )
SELECT INSTR(EMAIL_ID, '.',1,1) firstDOT_POSITION     
FROM CTE;

FIRSTDOT_POSITION

10

Query example to Search for a sub-string that does not exist in a string

WITH CTE(EMAIL_ID) AS
  (SELECT '[email protected]' AS EMAIL_ID 
   FROM DUAL
   )
 SELECT INSTR(EMAIL_ID, 'E') substring_location
 FROM CTE;

substring_location

0