Friday, October 15, 2004

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.

2 comments:

Anonymous said...

Simple, efficient, GREAT! Thanks...

Jason Bennett said...

Here is one that is recursive and in a single function:

CREATE OR REPLACE FUNCTION getStringElement(p_string VARCHAR2,
p_element NUMBER,
p_delimiter VARCHAR2 := ',',
p_level NUMBER := 0) RETURN VARCHAR2
IS

v_string VARCHAR2(2000) := NULL;
v_element VARCHAR2(2000) := NULL;
v_next VARCHAR2(2000) := NULL;

v_level NUMBER(4) := 0;


BEGIN

v_level := p_level + 1;

v_element := substr(p_string||p_delimiter,1,instr(p_string||p_delimiter,p_delimiter)-1);

-- need to look ahead to make sure we handle the null elements.
v_next := substr(p_string||p_delimiter,instr(p_string||p_delimiter,p_delimiter),length(p_delimiter));

IF ((v_level >= p_element) OR (v_element IS NULL AND v_next != p_delimiter)) THEN

RETURN v_element;

ELSE

v_string := substr(p_string||p_delimiter,instr(p_string||p_delimiter,p_delimiter)+1,length(p_string));

RETURN getStringElement(v_string,p_element,p_delimiter,v_level);

END IF;


END;
/

Example:

The statement:

SELECT getStringElement('This is an interesting test of recursion in PL/SQL',7,' ') from dual;

will return the value "recursion".