Split

Here is a small pipelined table function that gets one string that includes a delimited list of values, and returns these values as a table:

create or replace function split (i_str in varchar2, i_delimiter in varchar2 default ',') 
  return sys.odcivarchar2list
  pipelined
  as
    l_current_string varchar2(4000) := i_str;
    l_pos            binary_integer;
  begin
    if i_str is null then
        return;
    end if;
    loop
      l_pos := nullif(instr(l_current_string, i_delimiter), 0);
      pipe row(substr(l_current_string, 1, nvl(l_pos - 1, length(l_current_string))));
      exit when l_pos is null;
      l_current_string := substr(l_current_string, l_pos + length(i_delimiter));
  end loop;
end split;
/

Once the string is converted to a table, it is easy to manipulate it just like any “regular” table. For example:

select initcap(column_value) as name,
       length(column_value) as name_length
from   table(split('paris,london,rome,madrid'))
order  by column_value;

NAME                           NAME_LENGTH
------------------------------ -----------
London                                   6
Madrid                                   6
Paris                                    5
Rome                                     4

Note that the default delimiter is a comma, but other characters (or sub-strings) may be specified:

select * 
from table(split('Paris, France@@London, UK@@Rome, Italy@@Madrid, Spain',
                '@@'));

COLUMN_VALUE
--------------------
Paris, France
London, UK
Rome, Italy
Madrid, Spain