REPLACE()

The PL/SQL REPLACE() function searches in a given string for each occurrence of a substring and replace it with another substring or, if we don't specify a replacement, simply remove it:

replace(string, substr, [replacement])

We see it at work in an example that is the same code we have seen as example for INSTR() and SUBSTR() rewritten to take advantage of this handy function.

We have in a string a list of comma separated values, we want to print each value on a different line:

declare
  l_names varchar2(60) := 'Ann,Matt,Joe,Bill,Tom,Jeff';
  l_my_names varchar2(60); -- 1.
begin
  l_my_names := replace(l_names, ',', chr(10)); -- 2.
  dbms_output.put_line(l_my_names);
end;

1. Local buffer, not strictly required but it helps readability
2. The newline charachter has 10 as internal representation (on commonly used environments). We replace each comma with a newline.

So much easier, isn't it? Actually we have sort of cheated here, because we do not extract the values from the list, we just replace the separator.

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