Split Functionality

SQL> CREATE OR REPLACE type split_tbl AS   TABLE OF VARCHAR2(32767);

SQL> 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;
    l_value VARCHAR2(32767);
  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;

SQL>   SELECT split('Raju,Kishore,Swathi,Lars') FROM dual;

Above procees seems a bit clumsy. More over we can get this result with out creating a function.

SQL> SELECT REGEXP_SUBSTR ('Raju,Kishore,Swathi,Lars', '[^,]+', 1, level) val
        FROM DUAL
        CONNECT BY 
        level <= LENGTH(REGEXP_REPLACE('Raju,Kishore,Swathi,Lars','[^,]*'))+1;