Recursive PL/SQL String Parser

In an earlier blog entry, I posted a PL/SQL package called StringTools.  This package contained a routine called getListElement that would let a user retrieve the nth element in a delimited string.  The routine uses iteration (iterates over the passed string until the desired element at position 'n' is located) to get the desired list element.  I was bored today, so I decided to write a variation of that function that uses recursion instead of iteration.  The recursive version has only eleven lines of code (not counting variable declarations and block declaration tokens).  It's fast and useful! 

Here is the code ... give it a try!

/*

   Recursive PL/SQL function that returns the nth element in a delimited String.
   The default delimiter is a ',' (comma) and the level indicator defaults to 0.

*/

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_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);

   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".

5:59:55 PM