INSTR

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:

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:

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.

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:

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.

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

 

Leave a Reply

  • (will not be published)