INSTR() and SUBSTR()

A couple of useful function for PL/SQL strings.

INSTR() returns the offset of a substring in a string, where the first position in the string is identified by 1 - 0 means the requested substring is not part of the string. It is possible to specify from where to start to search, and which instance of the substring we are interested in:

instr(string, substr [, start [, nth]])

SUBSTR() extract a substring from a string from a specified internal position to the end of string or to another position:

substr(string, start [, length])

As an example of usage, let's consider this PL/SQL code:

declare
  l_names varchar2(60) := 'Ann,Matt,Joe,Bill,Tom,Jeff'; -- 1.
  l_my_names varchar2(61); -- 2.
  l_comma pls_integer := 0; -- 3.
  l_begin pls_integer; -- 4.
begin
  l_my_names := l_names || ','; -- 5.

  loop -- 6.
    l_begin := l_comma + 1; -- 7.

    l_comma := instr(l_my_names, ',', l_begin); -- 8.
    if l_comma = 0 then -- 9.
      exit;
    else -- 10.
      dbms_output.put_line(
        substr(l_my_names, l_begin, l_comma - l_begin));
    end if;
  end loop;
end;

1. We have a string containing a list of comma separated values, we want to print each value on a different line.
2. Buffer for internal usage, see (5) for details.
3. l_comma keeps track of the current comma position.
4. l_begin keeps track of the current substring beginning.
5. The resulting algorithm is simpler if we have a comma terminating every substring - you could modify the code to make this change not necessary, actually it is not a big deal, but this change makes the code easier to be understood.
6. I feel that a do-while (aka repeat-until) loop would have been the best choice here, but this kind of loop is not available for PL/SQL, so I used a simple loop instead.
7. l_begin is set to the next position after the previous comma. For the first loop we want to set it to 1, so I initialized l_comma to zero.
8. We use INSTR() to get the position of the next comma in the string, starting from l_begin.
9. If no comma is found, it is time to leave the loop.
10. Otherwise we output the current element: we get the substring in l_my_names starting on the current value for l_begin with length calulatede as the distance to the comma.

Chapter 8 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is all about strings datatypes and related functions.