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:
l_names varchar2(60) := 'Ann,Matt,Joe,Bill,Tom,Jeff';
l_my_names varchar2(60); -- 1.
l_my_names := replace(l_names, ',', chr(10)); -- 2.
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.