Please note that you can always click on an image in my postings and it will render a clear full sized version in a separate browser page! Also please note that this blog is best viewed with Firefox and Chrome

Sunday, September 16, 2007

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 )


    nPos1 number;

    nPos2 number;


   nPos1 := 0;

   nPos2 := 0;

   nPos1 := iStart;

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


   nPos2 = 0 then

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

      iNextPos := nPos2;


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

   iNextPos := nPos2 +1;


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

No comments:

Post a Comment