Definition of PL/SQL instr
PL/SQL provides the different types of function the user, in which that instr () function is one of the functions provided by the PL/SQL. Basically, the instr () function is used to display the position of the substring from a string that means it performs the search operation to return the substring. The instr () function is used to indicate the character in the string that means the very first character from the string or we can say that occurrence of characters. It is also used to calculate the string by using a character set as per our requirement. The substring may be char, varchar2, nchar, clob, or nclob data type that means the substring can be any data type.
Syntax:
instr(specified string, specified substring [, starting position[, Nth appearance position]])
Explanation
In the above syntax, we use the instr () function with different parameters as follows.
- specified string: specified string means a string in which we need to search the substring and it has any one of the data types char, varchar2, nchar, clob, etc.
- specified substring: It specifies the substring that we need to search in string and it has any one of the data types char, varchar2, nchar, clob, etc.
- starting position: It’s an optional argument that specifies the place in the text where the search should begin. 1 is the default value. If the value inserted is negative, the INSTR () function counts back to start position the number of characters from the end of the string and then looks towards the beginning of the string.
- Nth appearance position: It’s an optional argument that specifies the substring’s nth appearance. 1 is the default value.
How instr works in PL/SQL?
Now let’s see how the instr () function works in PL/SQL as follows.
Basically, the instr () function accepted four different parameters such as substring, string, starting position, and nth appearance. The function returns an integer reflecting the location of the first character of this occurrence in the string. INSTRC makes use of full Unicode characters. UCS2 code points are used in INSTR2. UCS4 code points are used by INSTR4. Starting location is a nonzero number that indicates where Oracle Database starts searching for a character in a string. When we need to search in the backward direction at that time we need to specify the negative number. Nth occurrence is a number that tells Oracle the occurrence of a string to look for. The occurrence value must be positive. CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB are all valid data types for string and substring.
Both location and occurrence must resolve to an integer and be of data type NUMBER, or any data type that may be automatically transformed to NUMBER. The default settings for both location and occurrence are 1, which means Oracle starts looking for the first occurrence of the substring at the first character of the string. If the search fails (if the substring does not exist many times after the string’s position character), the return result is 0.
4.5 (8,882 ratings)
View Course
Examples
Now, let’s give a different example of the instr () function in PL/SQL for better understanding as follows.
SET SERVEROUTPUT ON;
DECLARE
Sample_String string(50) := 'Welcome in first PL/SQL program';
BEGIN
dbms_output.put_line(INSTR(Sample_String, 'p'));
END;
/
Explanation
In the above example first, we set server output on, after that in the declaration section we declare the string as shown in the above example. Here we use the Sampl_string variable to store the string and in the execution section, we write the procedure to display the position of the substring. In this example we implement the forward character search, here we need to find the position in the “P” character that we call substring. The final output of the above example we illustrated by using the following screenshot as follows.
Now let’s see another example of the instr () function as follows.
SET SERVEROUTPUT ON;
DECLARE
Sample_String string(50) := 'Welcome in first PL/SQL program';
BEGIN
dbms_output.put_line(INSTR(Sample_String, 'i', 1, 1));
END;
/
Explanation
In the above example first, we set server output on, after that in the declaration section we declare the string as shown in the above example. Here we use the Sampl_string variable to store the string and in the execution section, we write the procedure to display the position of the substring. In this we try to implement the instr () function with character position, here we need to search the “i” character and we pass the default value of starting position and appearance position as shown in the above example. The final output of the above example we illustrated by using the following screenshot as follows.
Now let’s see another example of the instr () function to search substring in a particular position as follows.
SET SERVEROUTPUT ON;
DECLARE
Sample_String string(50) := 'WelcomeinfirstPL/SQ program';
BEGIN
dbms_output.put_line(INSTR(Sample_String, 'o', 1, 6));
END;
/
Explanation
In the above example first, we set serveroutput on, after that in the declaration section we declare the string as shown in the above example. Here we use the Sampl_string variable to store the string and in the execution section, we write the procedure to display the position of the substring. In this example, we need to find the substring at the 6th position with a forwarding search. The final output of the above example we illustrated by using the following screenshot as follows.
Now let’s see another example instr () function with backward search as follows.
SET SERVEROUTPUT ON;
DECLARE
Sample_String string(50) := 'WelcomeinfirstPL/SQ program';
BEGIN
dbms_output.put_line(INSTR(Sample_String, 'in', -2, 1));
END;
/
Explanation
In this example we try to implement the instr() function with a backward position as shown. . The final output of the above example we illustrated by using the following screenshot as follows.
SET SERVEROUTPUT ON;
DECLARE
Sample_String string(50) := 'WelcomeinfirstPL/SQ program';
BEGIN
dbms_output.put_line(INSTR(Sample_String, 'first', 1, 1));
END;
/
Explanation
In this example, we need to find the position of the “first” substring as shown. The final output of the above example we illustrated by using the following screenshot as follows.
Conclusion
We hope from this article you learn PL/SQL instr. From the above article, we have learned the basic syntax of instr and we also see different examples of the instr. From this article, we learned how and when we use PL/SQL instr.
Recommended Articles
This is a guide to PL/SQL instr. Here we discuss the definition, how the instr () function works in PL/SQL, and examples, respectively. You may also have a look at the following articles to learn more –
