Oracle INSTR Function: Uses & Examples

The INSTR() function in Oracle database is a commonly used string function that is used to search for a substring within a string and return the position of the substring in the parent string. Here are some common use cases of the INSTR() function:

1. Find substring position: Locate the first occurrence of a specified substring within a parent string by specifying both the substring and the parent string.
2. What is the position of ‘lo’ in the string ‘hello world’?
   — The return value is 4.
3. Find the position of the nth occurrence: In addition to finding the position of the first occurrence, you can also specify the third parameter to find the position of the nth occurrence.
4. — The returned value is 20, indicating the position of the third occurrence of a comma in the string.
5. Search from right to left: By using negative numbers as the starting position, you can search for the position of a substring from right to left.
6. The output of the query will be 8, indicating the position of the first occurrence of ‘o’ when searching from right to left in the phrase ‘hello world’.
7. Combining with other string functions: The INSTR() function is commonly used in conjunction with other string functions, such as SUBSTR(), LENGTH(), etc., to meet more complex string processing requirements.

Overall, the INSTR() function is commonly used in the Oracle database for handling string-related operations such as searching, locating, and splitting.