Oracle: functions to join and split strings in sql

Join:

create or replace
function join
(
    p_cursor sys_refcursor,
    p_del varchar2 := ','
) return varchar2
is
    l_value   varchar2(32767);
    l_result  varchar2(32767);
begin
    loop
        fetch p_cursor into l_value;
        exit when p_cursor%notfound;
        if l_result is not null then
            l_result := l_result || p_del;
        end if;
        l_result := l_result || l_value;
    end loop;
    return l_result;
end join;
 /

Now, you can run a query like this:

select join(cursor(select ename from emp))from dual;

Split:

create or replace TYPE "SPLIT_TBL" as table of varchar2(32767);
/

create or replace
FUNCTION split(
      p_list VARCHAR2,
      p_del  VARCHAR2 := ',' )
    RETURN split_tbl pipelined
  IS
    l_idx pls_integer;
    l_list VARCHAR2(32767):= p_list;
  BEGIN
    LOOP
      L_IDX   :=INSTR(L_LIST,P_DEL);
      IF l_idx > 0 THEN
        pipe row(SUBSTR(l_list,1,l_idx-1));
        l_list:= SUBSTR(l_list,l_idx  +LENGTH(p_del));
      ELSE
        pipe row(l_list);
        EXIT;
      END IF;
    END LOOP;
    RETURN;
  END split;
 /

Now, you can run a query like this:  

SQL> select * fromtable(split('one,two,three'));


one
two
three

Ref: create functions to join and split strings in sql