PLSQL doesn't include any functions to split strings by tokens or delimiting characters such as commas or colons.

The function below allows you to split a string separated by a specified character. It returns the chunk that you request.

myStr := SPLIT_STRING_BY_TOKEN('red:blue:green',':',2)

This function call above returns the 2nd chunk in the string which results in 'blue'.

CREATE OR REPLACE FUNCTION SPLIT_STRING_BY_TOKEN(pStr IN VARCHAR2,

                                        pToken IN VARCHAR2,

                                        pChunk IN VARCHAR2 DEFAULT 1) RETURN VARCHAR2 AS 



  -- 23/04/2010 www.mattsbits.co.uk

  -- This function returns a chunk of a string delimited with pToken

  -- pStr - String to search

  -- pToken  - Single delimiting character to split by



  TYPE t_array IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER; 



  intStart NUMBER;  -- Start of chunk

  intStop  NUMBER;  -- End of chunk

  intChunk NUMBER;  -- Chunk counter



  chunks t_array;   -- Array to hold chunks



BEGIN



   intChunk := 0;

   intStart := 1;

   intStop  := instr(pStr,pToken); 



   -- Continue while there are chunks to find

   WHILE (intStop != 0) LOOP 



     -- Increment chunk counter 

     intChunk := intChunk + 1; 



     -- Get chunk

     chunks(intChunk) := substr(pStr,intStart,intStop-intStart);



     -- Reset positions

     intStart := intStop+1;

     intStop  := instr(pStr,pToken,intStart);      



     -- If at the end of the string get last chunk

     IF intStop=0 THEN

       intChunk := intChunk + 1; 

       chunks(intChunk) := substr(pStr,intStart,length(pStr)-intStart+1);

     END IF;



  END LOOP;

  

  IF pChunk>intChunk OR pChunk<1 THEN

    -- No chunk exists

    RETURN '';

  ELSE

    -- Return requested chunk

    RETURN chunks(pChunk);

  END IF;



END SPLIT_STRING_BY_TOKEN;

This function is useful if you've got a list of parameters in a known order that you need to extract.

It also deals with empty entries. ie where the delimiter appears next to each other.

The delimiting character is always treated as a delimiter to make sure your data doesn't contain it.