INSTRB
INSTRB is a function in SQL that allows users to search and locate a substring within a given string. Unlike the INSTR function that counts characters, INSTRB counts bytes. It returns the position of the first occurrence of the substring in bytes from the beginning of the string.
INSTRB(string, substring, start_position, nth_occurrence)
- string: This parameter indicates the string in which the search will be performed. It is the main body of text where the substring might be located.
- substring: This parameter specifies the sequence of characters that will be sought within the main string. It is the smaller text sequence whose position within the main string is to be found.
- start_position: This parameter indicates the starting point within the main string from where the search for the substring begins. Counts the string in bytes from the start. The count begins from 1.
- nth_occurrence: This parameter specifies the occurrence number of the substring within the string. For example, if ‘nth_occurrence’ is 2, the function will return the position of the second occurrence of the substring within the string.
Example
SELECT INSTRB('Hello, World!', 'o') as Position FROM dual;Output
POSITION--------5Explanation
INSTRB function in Oracle is used to search for a substring in a string and returns the position in bytes of the first occurrence of the specified substring. In this example, it returns the position of the first occurrence of the character ‘o’ in the string ‘Hello, World!’, which is at the position 5.