A detailed explanation and application of the instr() function in Oracle.

2 years ago
Benjamin Taylor
2 minutes

The instr() function in Oracle is used to find the position of a specified substring within a string. Its syntax is as follows:

INSTR(string, substring [, start_position [, nth_occurrence]])

Explanation of parameters:

  1. string: the string to be searched.
  2. substring: the substring to be searched for.
  3. start_position: an optional parameter that specifies the position to start searching from, with a default value of 1.
  4. nth_occurrence: an optional parameter that specifies the position of the substring within the string to search for, with the default being the first occurrence.

Return value:

  1. If a substring is found, return its position in the string.
  2. If the substring is not found, return 0.

Here are some common usage examples:

  1. Find the position of a substring:
  2. The output would be 8 when searching for the position of ‘Database’ within the string ‘Oracle Database’.
  3. Find the position of the substring starting from a given location.
  4. The code will return the position of the letter ‘a’ in the string ‘Oracle Database’, starting from the 4th character.
    — Result: 7
  5. Find the position of the Nth occurrence of a substring.
  6. The position of the second occurrence of the letter ‘a’ in the string ‘Oracle Database’ is 16.
  7. Return 0 if the substring is not found.
  8. The above query will return the position of ‘SQL’ in the string ‘Oracle Database’. The result is 0.

Besides finding the position of a substring, the instr() function can also be used for other purposes, such as:

  1. Replace a part of the string:
    SELECT REPLACE(‘ABCDEF’, ‘CD’, ‘XY’) AS replaced_string FROM dual;
    — Result: ABXYEF
  2. Extracting substring:
    SELECT SUBSTR(‘Oracle Database’, 8, 8) AS substring FROM dual;
    — Result: Database

Summary:
The instr() function in Oracle is used to find the position of a subtring within a string, allowing for the specification of a starting position and search order. It is a commonly used string function that can be used in various scenarios.

bannerAds