Split String and Get as Table Type in Oracle

CREATE OR REPLACE FUNCTION SPLIT_STRING(P_LIST VARCHAR2,
                                        P_DEL  VARCHAR2 DEFAULT ',')
  RETURN SPLIT_TBL
  PIPELINED IS
  /*********************************************************
  This Function will split the string and return as table type.
  Created by :- Ajit Kumar Dubey
  Date :- 12-Sep-2014
  ----------------------------------------------------------
  Create a type as given below for this function.
 
  create or replace type split_tbl as table of varchar2(32767);
  *********************************************************/
  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_STRING;

add to del.icio.us saved by 0 users