Updated March 3, 2023
Introduction to Oracle INSTR()
INSTR ( ) functions (INSTR, INSTRB, INSTRC, INSTR4, and INSTR2) in Oracle can be defined as a function which is used to find the position of a substring present inside the string which means that the function INSTR ( ) searches the whole string for the mentioned substring and then it returns the position of the first occurrence of the character of the substring present inside the string and the function returns a non zero integer when the search is true and it returns zero when the search is false.
Syntax
Let u now discuss the syntax of the INSTR ( ) function
INSTR (original_string, substring [, start_position [, occurence] ]
Parameters
- original_string: It refers to the string which we want to search.
- substring: It refers to the substring which we want to search in the string.
- start_position: It is optional and it generally refers to the position in the original string from where the search will start. If no value is provided the default value is set to one. The default value is 1.
- occurrence: It is optional and it is generally an integer which indicates the occurrence of the substring in the string that the INSTR function should search for.
How Does INSTR( ) Function Work in Oracle?
The working of the INSTR ( ) function in oracle is quite simple. We have to enter compulsorily two arguments as parameters in the INSTR function. The first parameter is the string that we want to search and the second parameter is the substring that we want to search inside the string. So, as soon as we execute the function it searches for the substring mentioned inside the function and if the substring equal to the passed substring is found in the string then the function returns a non zero integer indicating the position of the first character of the substring and in case no match is found in the string then it returns the integer 0. The default starting position is one but in case the user wants to search the string from a particular position, He can do so by using the optional parameter start_position as mentioned in the PARAMETERS section before in this article.
Examples to Implement Oracle INSTR()
We will now go through various examples in this section to have a better understanding of the function.
1. INSTR ( ) function on dual table
We are aware of the fact that dual is a dummy table in SQL that is automatically created by the database. So in this example we are going to find a substring named ‘Good’ in the string ‘Rohan is a good boy’. We are going to use the INSTR function for this example.
Let us look at the query below
Code:
SELECT INSTR ('Rohan is a Good boy', 'Good') "Position"
FROM DUAL;
In the above query we can see the first parameter the string is passed which we want to search and in the second parameter the substring is passed.
Output: Let us run the query in SQL developer and check the result.
As we can see that the Position for the substring is 12.
2. INSTR ( ) function with optional start position parameters
We are going to look into the behavior of the INSTR ( ) function with the optional start position parameter as an input parameter to the function. In this example we are going to search the substring from the third position of the original string. Here the original string is “the sun rises in the east” and the substring to search is “the”. Let us look at the query for the same.
Code:
SELECT INSTR ('the sun rises in the east', 'the', 2) "Position"
FROM DUAL;
In the above query we can see that we have given the position two which means that from the second position of the string the function will start the search.
Output: Let us execute the query in SQL developer and look at the result.
As we can see in the above screenshot the function returns the position of the substring.
3. INSTR ( ) function with all optional parameters
We are going to take a look into a case where we are passing all the four parameters inside the function i.e. string, substring, start_position, and occurrence. All these are already discussed in the parameter section. In this example we are going to search for the second occurrence of the substring starting backward. Here the string used will be “the sun rises in the east” and the substring is “the”. Let us look at the query for the same.
Code:
SELECT INSTR ('the sun rises in the east', 'the', -1, 2) "Position"
FROM DUAL;
In the above query, the value -1 represents that the string starts from backward and the value2 represents the second occurrence of the substring.
Output: Let us execute the query in SQL developer and check the result.
As we can see in the output the function returns the position of the second occurrence of the substring starting backward.
4. INSTR ( ) function with false match
We are going to look into the case where the substring is not present in the string. In this example we are going to search for a substring that is not present in the string. The string is “The sun rises in the east” and the substring is “west”. Let us look at the query for the same.
Code:
SELECT INSTR ('the sun rises in the east', 'west') "Position"
FROM DUAL;
Output: Let us execute the query in SQL developer and check the result.
In the above screenshot we can see that since there is no substring “west” in the string. The function returns the value zero.
Conclusion
In this article we discussed the definition of the INSTR ( ) function and also the syntax of the function. In the article we also discussed the various cases of the function and also tried to get a better understanding of it through the help of examples.
Recommended Articles
This is a guide to Oracle INSTR(). Here we discuss an introduction to Oracle INSTR(), syntax, how does it works with examples for better understanding. You can also go through our other related articles to learn more –
