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

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.