INSTR examples in Oracle

Different uses of INSTR function in Oracle are shown here.

Example-1: use of INSTR from the beginning of the string


SELECT
INSTR( 'Eat to live, not live to eat', 'eat' ) TEXT
FROM
dual;

Output:

p4

Example-2: use of INSTR with positions and occurrence


SELECT
INSTR( 'Eat to live, not live to eat', 'live', 5, 2) TEXT
FROM
dual;

Output:

p5

Example-3: use of INSTR with table


SELECT
Acc_name
FROM
members
WHERE INSTR( acc_create_date, 'NOV' ) > 0 ;

p6

You will get the explanation in the following video.

Exercise: Create a table named customers (id, name, address, mobile) with some data. Search those customer records who have ‘Robi’ mobile number using INSTR.