Parsing delimited fields in a character string

Often we have a need to parse a character string to get data from fields within it. Of course, SQL Loader handles this nicely, but sometimes we may be getting the data via a different route such as from a table or via UTL_FILE.

The following package facilitates this:

CREATE OR REPLACE PACKAGE parse AS
  /*
  || Package of utility procedures for parsing delimited or fixed position strings into tables
  || of individual values, and vice versa.
  */
  TYPE varchar2_table IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
  PROCEDURE delimstring_to_table
    ( p_delimstring IN VARCHAR2
    , p_table OUT varchar2_table
    , p_nfields OUT INTEGER
    , p_delim IN VARCHAR2 DEFAULT ','
    );
  PROCEDURE table_to_delimstring
    ( p_table IN varchar2_table
    , p_delimstring OUT VARCHAR2
    , p_delim IN VARCHAR2 DEFAULT ','
    );
END parse;
/
CREATE OR REPLACE PACKAGE BODY parse AS
  PROCEDURE delimstring_to_table
    ( p_delimstring IN VARCHAR2
    , p_table OUT varchar2_table
    , p_nfields OUT INTEGER
    , p_delim IN VARCHAR2 DEFAULT ','
    )
  IS
    v_string VARCHAR2(32767) := p_delimstring;
    v_nfields PLS_INTEGER := 1;
    v_table varchar2_table;
    v_delimpos PLS_INTEGER := INSTR(p_delimstring, p_delim);
    v_delimlen PLS_INTEGER := LENGTH(p_delim);
  BEGIN
    WHILE v_delimpos > 0
    LOOP
      v_table(v_nfields) := SUBSTR(v_string,1,v_delimpos-1);
      v_string := SUBSTR(v_string,v_delimpos+v_delimlen);
      v_nfields := v_nfields+1;
      v_delimpos := INSTR(v_string, p_delim);
    END LOOP;
    v_table(v_nfields) := v_string;
    p_table := v_table;
    p_nfields := v_nfields;
  END delimstring_to_table;
  PROCEDURE table_to_delimstring
    ( p_table IN varchar2_table
    , p_delimstring OUT VARCHAR2
    , p_delim IN VARCHAR2 DEFAULT ','
    )
  IS
    v_nfields PLS_INTEGER := p_table.COUNT;
    v_string VARCHAR2(32767);
  BEGIN
    FOR i IN 1..v_nfields
    LOOP
      v_string := v_string || p_table(i);
      IF i != v_nfields THEN
        v_string := v_string || p_delim;
      END IF;
    END LOOP;
    p_delimstring := v_string;
  END table_to_delimstring;
END parse;
/

This is how you might use it with a standard comma-delimited string:

SQL> declare
  2    v_tab parse.varchar2_table;
  3    v_nfields integer;
  4    v_string varchar2(1000) := '1000,Smith,John,13-May-1970';
  5  begin
  6    parse.delimstring_to_table (v_string, v_tab, v_nfields);
  7    for i in 1..v_nfields loop
  8      dbms_output.put_line('Field('||i||') = '||v_tab(i));
  9    end loop;
 10  end;
11 /
Field(1) = 1000
Field(2) = Smith
Field(3) = John
Field(4) = 13-May-1970

PL/SQL procedure successfully completed.