The PLSQL INSTRC function is used for returning the location of a substring in a string, Unicode complete characters. The PLSQL INSTRC function searches a string for a substring specified by the user using characters and returns the position in the string that is the first character of a specified occurrence of the substring.
The PLSQL INSTRC function accepts four parameters which are string, substring, start position and the nth appearance. The string and substring can be of any of the datatypes such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
Syntax:
INSTRC(string, substring [, start_position [, nth_appearance ]])
Parameters Used:
string – It is used to specify the string in which the substring needs to be searched. It can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
substring – It is used to specify the substring which needs to be searched. It can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
start_position – It is an optional parameter which is used to specify the position in the string from where the search will start. The default value is 1. The INSTRC function counts back to start_position the number of characters from the end of the string and then searches towards the beginning of string if the value inserted is negative.
nth appearance – It is an optional parameter which is used to specify the nth appearance of substring. The default value is 1.
Supported Versions of Oracle/PLSQL:
- Oracle 12c
- Oracle 11g
- Oracle 10g
- Oracle 9i
- Oracle 8i
Example-1: Using Character to Search Forward to Find the Position of a Substring.
DECLARE Test_String string(20) := 'Geeksforgeeks'; BEGIN dbms_output.put_line(INSTRC(Test_String, 'e')); END;
Output:
2
Example-2: Using Character Position to Search Forward to Find the Position of a Substring.
DECLARE Test_String string(20) := 'Geeksforgeeks'; BEGIN dbms_output.put_line(INSTRC(Test_String, 'e', 1, 1)); END;
Output:
2
Example-3: Using Character Position to Search Forward to Find the Position of a Substring in the 3rd position.
DECLARE Test_String string(20) := 'Geeksforgeeks'; BEGIN dbms_output.put_line(INSTRC(Test_String, 'e', 1, 3)); END;
Output:
10
Example-4: Using Character Position to Search Backward to Find the Position of a Substring.
DECLARE Test_String string(20) := 'Geeksforgeeks'; BEGIN dbms_output.put_line(INSTRC(Test_String, 'e', -2, 1)); END;
Output:
11
Example-5: Using a Triple-Byte Character Set to Find the Position of a Substring.
DECLARE Test_String string(20) := 'Geeksforgeeks'; BEGIN dbms_output.put_line(INSTRC(Test_String, 'for', 1, 1)); END;
Output:
6
Similar Reads
PLSQL | INSTR Function
The PLSQL INSTR function is used for returning the location of a substring in a string. The PLSQL INSTR function searches a string for a substring specified by the user using characters and returns the position in the string that is the first character of a specified occurrence of the substring. The
2 min read
PLSQL | INSTRB Function
The PLSQL INSTRB function is used for returning the location of a substring in a string, using bytes instead of characters. The PLSQL INSTRB function searches a string for a substring specified by the user using characters and returns the position in the string that is the first character of a speci
2 min read
PLSQL | INSTR2 Function
The PLSQL INSTR2 function is used for returning the location of a substring in a string using UCS2 code points. UCS-2 codepoints is a character encoding standard in which characters are represented by a fixed-length 16 bits (2 bytes). UCS-2 represents a possible maximum of 65, 536 characters, or in
3 min read
PLSQL | INSTR4 Function
The PLSQL INSTR4 function is used for returning the location of a substring in a string using UCS4 code points. UCS-4 codepoints is a character encoding which allows the representation of each value as exactly four bytes (one 32-bit word). UCS-4 represents a possible value between 0 and hexadecimal
3 min read
PLSQL | INITCAP Function
The INITCAP function in PLSQl is used for setting the first character in each word to uppercase and the rest to lowercase. Words are delimited by white space or characters that are not alphanumeric. The INITCAP function in PLSQL can accept char can of any of the datatypes such as CHAR, VARCHAR2, NCH
1 min read
PLSQL | NCHR Function
The PLSQL NCHR function is used for returning the character based on the number code in the national character set or in other words it returns the binary equivalent to the number in the national character set. The value returned by the NCHR function is always of the datatype NVARCHAR2. The NCHR fun
1 min read
PLSQL | LENGTHC Function
The PLSQL LENGTHC function is used for returning the length of the specified string using UNICODE complete characters. The char accepted by the LENGTHC function in PLSQL can be of any of the datatypes such as CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The value returned by the LENGTHC functio
1 min read
PLSQL | LN Function
The LN function is an inbuilt function in PLSQL which is used to return the natural logarithm of a given input number. The natural logarithm of a number is the logarithm of that number to the base e, where e is the mathematical constant approximately equal to 2.718. This is written using the notatio
2 min read
PLSQL | LEAST Function
The LEAST is an inbuilt function in PLSQL which is used to return the least value from a given list of some expressions. These expressions may be numbers, alphabets etc. Syntax: LEAST(exp1, exp2, ... exp_n) Parameters Used: This function accept some parameters like exp1, exp2, ... exp_n. These each
2 min read
PLSQL | LTRIM Function
The PLSQL LTRIM function is used for removing all specified characters from the left-hand side of a string. The PLSQL LTRIM function accepts two parameters which are input_string and trim_string. If the user does not specify trim_string, it defaults to a single blank. If char is a character literal,
2 min read