Jason Bennett's Developer Corner

 






Click to see the XML version of this web page.




View David Jason Bennett's profile on LinkedIn

 

 

A Little About Jason Bennett ...

I've had an interest in publishing technical articles and HELPFUL code for a few years.  I am (by trade and hobby) a developer who specializes in Oracle technologies and web based architectures.  I have been an employee of both TUSC and Oracle Corporation.  My intent here is to share my ideas and coding experiences with the developer community as a whole.  As with all developers some of my ideas are great and some of them are ....  well you know.  Anyway, I hope you find something here that will aid in your endeavor, or spark a new idea. 

I am more than happy to assist with technical issues and will even write a little code if need be. If you find something on the site that is really useful and you'd like to make a contribution (absolutely up to you and absolutely not required), just click the "Make a Donation" button on the left!

Good luck and good coding !




  Saturday, April 12, 2008


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    

Click here to visit the Radio UserLand website. © Copyright 2009Jason Bennett.
Last update: 3/11/2009; 4:55:05 PM.

April 2008
Sun Mon Tue Wed Thu Fri Sat
    1 2 3 4 5
6 7 8 9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30      
Mar   Jun