Oracle INSTR

INSTR is a special string function that returns the position of ‘text’ in a string, if the ‘text’ does not exist then it returns 0.

Syntax:

The syntax of the Oracle INSTR function is as follows:

INSTR ( Expression, 'text' )

Or

INSTR( Expression , 'text' [, x [, y])

Here,

Expression – Expression could be a string or a column name containing text values. The data type of this expression must be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

‘text’ – Specific text value that we are looking for in an Expression

x – Starting position of text value ( Optional)

If the value of x is positive. then it will search forward in the string from the beginning of string. If the value of x is negative then it will search backwards from the ending of string.

y – y represents the occurrence of text value as a text value can occur multiple times in an expression. ( Optional )

If we don’t enter the value of x, then by default the query would return the first occurrence starting from first position.

If we don’t enter the value of y, then query would return the first occurrence by default from the position we have specified.

Return Value

The query returns a positive integer that represents the position of ‘text’ value in the string. It returns zero if the ‘text’ value is not present in string.

Oracle INSTR Examples

As we can see, from the example that text value ‘is’ repeats three times in the string ‘His Dad is a typist’. We use the INSTR function to find the location of each occurrence.

1. Searching for the first occurrence of ‘text’ value in an Expression

If we want to find the location of the first occurrence of text value = ‘is’ in a string = ‘His Dad is a Typist.’, then we use this query:

SELECT
INSTR( 'His Dad is a Typist.', 'is' ) text_position
FROM
dual;

Result:

The query returns the position of first occurrence of word ‘is’ in the string ‘His Dad is a Typist’.

For this example, we will be using Employees Table from HR schema.

Similarly, if we want to find the position of ‘text value’ =’A’ across multiple rows in a column then we use this query:

SELECT
first_name, INSTR( first_name, 'A' ) text_position
FROM
employees;

Result:

The query returns 1 for every employee whose first name starts with ‘A’ and 0 if it doesn’t.

2. Search for 2nd and 3rd occurrence of ‘text’ value in String

We can also give the values of x and y, where x is the starting position and y is the number of occurrence of ‘text value’ in a string.

To find the second and third occurrence of text value = ‘is’ in the string = ‘His Dad is a Typist.’, we use this query:

SELECT
INSTR( 'His Dad is a Typist.', 'is', 1, 2 ) second_occurrence,
INSTR( 'His Dad is a Typist', 'is', 1, 3 ) third_occurrence
FROM
dual;

Result:

The query returns the position of second and third occurrence of word ‘is’ in the string.

3. Search for a text value that does not exist in Expression

Suppose, we enter a text value that does not exist in a string using this query:

SELECT 
INSTR( 'His Dad is a Typist.', 'She' ) text_position
FROM
dual;

Result:

The query returns zero as the text value that we want to find does not exist in the string.

4. Search for a ‘text’ value from end of string

Sometimes, we may need to find the occurrence of text value from backwards of a string, then we use a negative value of x:

SELECT
INSTR( 'His Dad is a Typist.', 'is', -1 ) text_position
FROM
dual;

Result:

The query returns the position of first occurrence of text value ‘is’ from backwards.

In this tutorial, we learned how to use INSTR function to find the position of text value in string.