INSTR Function:

To understand INSTR function, it has 4 components in that function:

INSTR (String, sub-string, starting character position, occurrence of the character);

• String: The string that is being used to search in.
• Sub-String: The character or the group of characters that is being searched for.
• Starting character position: The starting position from where the string has to be searched from
• Occurence: The nth occurence of the Character or the group of characters.

For Example:

select instr ('test_element_is_titanium','t',1,3) from dual;

Here, in the string, we are trying to find the position of the 3rd occurence of the alphabet ‘t’ starting from the first position. Result: 12

Example 2:

select instr ('test_element_is_titanium','t',5,3) from dual;

Here, we are trying to find the position of the 3rd occurence of the alphabet ‘t’ starting from the 5th position. Result: 19

Example 3:

It is case sensitive.

select instr ('Test_Element_is_Titanium','t',1,1) from dual;

Here, we are trying to find the position of 1st occurence of the alphabet – lower case ‘t’ – starting from the 1st position. Result: 4

Example 4:

select instr ('test_element_is_titanium','t',1,8) from dual;

If the INSTR function is not able to find the occurence, then it results in zero (0). Since there is not 8th occurence of alphabet ‘t’ Result: 0

Real Life Usage: 

In real life, there may be a situation where we may have a string from which we are asked to retrieve the Customer Name (bolded alphabets in the string below).

String is: ‘12345678;Test_Customer_1;1234 Main Street;New York;94949;US’

The logic will be to identify the characters between the occurrence of the first semi-colon and the second semi-colon using INSTR and then using those positions in the substr function.

SELECT SUBSTR (
           '12345678;Test_Customer_1;1234 Main Street;New York;94949;US',
             INSTR (
                 '12345678;Test_Customer_1;1234 Main Street;New York;94949;US',
                 ';',
                 1,
                 1)
           + 1,
             INSTR (
                 '12345678;Test_Customer_1;1234 Main Street;New York;94949;US',
                 ';',
                 1,
                 2)
           - INSTR (
                 '12345678;Test_Customer_1;1234 Main Street;New York;94949;US',
                 ';',
                 1,
                 1)
           - 1)
  FROM DUAL;

Run the query above – Which gives the result as – Test_Customer_1