cat brain.stuff > wordpress

CREATE OR REPLACE TYPE my_array IS varray(1000) OF VARCHAR2(255);
 
CREATE OR REPLACE FUNCTION my_split(p_string IN VARCHAR2, p_delim IN VARCHAR2)
RETURN my_array
AS
    p_last_index NUMBER := 1;
    p_current_index NUMBER := 1;
 
    p_array_pointer NUMBER := 1;
    p_items my_array := my_array();
    p_item VARCHAR2(255);
BEGIN
     -- get index of split character
     p_last_index := INSTR(p_string,p_delim,p_current_index,1);
 
     -- while split characters are found
     -- add it to the varray
     WHILE( p_last_index > 0 ) LOOP
    	-- get first item
        p_item := SUBSTR(p_string, p_current_index, (p_last_index - p_current_index));
 
	-- put item in varray
        p_items.extend;
        p_items(p_array_pointer) := p_item;
        p_array_pointer := p_array_pointer + 1;
 
    	-- update indexes
        p_current_index := p_last_index + LENGTH(p_delim);
        p_last_index := INSTR(p_string,p_delim,p_current_index,1);
    END LOOP;
 
    -- get last item
    p_item := SUBSTR(p_string, p_current_index);
    -- put item in varray
    p_items.extend;
    p_items(p_array_pointer) := p_item;
 
    --dbms_output.put_line(substr('Value of p_receiver='||p_receiver,1,255));
 
    /*
    Example of how to loop through the items:
 
    for a_index in 1..p_items.count loop
    	dbms_output.put_line(substr('Value of array('||a_index||'):'||p_items(a_index),1,255));
    end loop;
    */
 
    RETURN p_items;
END;

DECLARE
	test my_array;
	input VARCHAR2(255) := 'a;b;c';
	delim VARCHAR2(1) := ';';
BEGIN
	test := my_split(input,delim);
 
	FOR a_index IN 1..test.COUNT LOOP
		DBMS_OUTPUT.put_line(SUBSTR('Value of array('||a_index||'):'||test(a_index),1,255));
	END LOOP;
END;