INSTRB is one of the vital string/char functions of Oracle. It is used to get the location of a substring, where a substring is a part of a string. It works same as INSTR4 function with the only difference that it uses bytes instead of characters. The INSTRB function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i and Oracle 8i.
Syntax:
INSTRB( string, substring, start_position, nth_appearance )
Parameters:
string: It is used to specify the string to set.
substring: It is used to specify the substring to search for.
start_position: It is an optional parameter which is used to specify the position in string where the search will start. Its default value is 1, i.e, the first position. It also accepts negative value, and in that case it counts back from the end of string and then starts the search backwards towards the beginning of the string. However, whatever be the case the value of the position will be same as counted from the start, i.e, the first position belongs to the first character of the string only the start position is from the beginning or the end.
nth_appearance: It is also an optional parameter which is used to specify the nth appearance of the substring. Its default value is 1.
Example 1:
INSTRB (‘HELLOWORLD.COM’, L) |
Output:
3
Explanation:
The first occurence of ‘L’ from the beginning of the given string is at the third position.
Example 2:
INSTRB (‘HELLOWORLD.COM’, L, 1, 2) |
Output:
4
Explanation:
The second occurence of ‘L’ from the beginning of the given string is at the fourth position.
Example 3:
INSTRB (‘HELLOWORLD.COM’, L, -1, 1) |
Output:
9
Explanation:
The first occurence of ‘L’ from the end of the given string is at the ninth position.
Example 3:
INSTRB (‘HELLOWORLD.COM’, L, -3, 1) |
Output:
9
Explanation:
The first occurence of ‘L’ from the third position from the end of the given string is at the ninth position.