Oracle PL/SQL Tokenizer and String Formatting

Several times in my past projects I needed to do some complex actions on Strings or Varchars.  Doing this in Java is a no-brainer with the Tokenizer.  Sadly, I either didn't look hard enough or didn't see it in the PL/SQL base packages, but I created a nice little tokenizer that solved my problems.  I've used this little nifty code bit several times now.

-----------------------------------------

--This is the tokenizer proc that is called several times that breaks down the strings by

--using the input parameters of 1. where to start, 2. the delimiting character or string

--and 3. the string to be broken down. It returns the extracted piece, and also the next

--position in the string so if the tokenizer needs to be called again, it will automatically

--start at the next position to extract the subsequent piece.

--Gregory Partenach v1 October 2003



PROCEDURE tokenizer ( iStart IN NUMBER,

                                        sPattern IN VARCHAR2,

                                        sBuffer IN VARCHAR2,

                                        sResult OUT VARCHAR2,

                                        iNextPos OUT NUMBER )

IS

    nPos1 number;

    nPos2 number;

BEGIN

   nPos1 := 0;

   nPos2 := 0;

   nPos1 := iStart;

   nPos2 := Instr (sBuffer, sPattern, iStart);

IF

   nPos2 = 0 then

      sResult := Rtrim(Ltrim(Substr(sBuffer, nPos1)));

      iNextPos := nPos2;

ELSE

   sResult := Substr(sBuffer, nPos1, nPos2 - nPos1);

   iNextPos := nPos2 +1;

END IF;

END tokenizer; -----------------------------------------