split string to array [ 835 views ]
Goal: create array from a long string
CREATE OR REPLACE PACKAGE STRING_FNC IS
TYPE t_array IS TABLE OF VARCHAR2(32000) INDEX BY BINARY_INTEGER;
FUNCTION SPLIT (p_in_string long, p_delim VARCHAR2) RETURN t_array;
END;
/
CREATE OR REPLACE PACKAGE BODY STRING_FNC IS
FUNCTION SPLIT (p_in_string long, p_delim VARCHAR2) RETURN t_array IS
i number :=0;
pos number :=0;
lv_str varchar2(32000) := p_in_string;
strings t_array;
BEGIN
pos := instr(lv_str,p_delim,1,1); -- determine first chuck of string
WHILE ( pos != 0) LOOP -- while there are chunks left, loop
i := i + 1; -- increment counter
strings(i) := substr(lv_str,1,pos); -- create array element for chuck of string
lv_str := substr(lv_str,pos+1,length(lv_str)); -- remove chunk from string
pos := instr(lv_str,p_delim,1,1); -- determine next chunk
IF pos = 0 THEN -- no last chunk, add to array
strings(i+1) := lv_str;
END IF;
END LOOP;
RETURN strings; -- return array
END SPLIT;
END;
/
Tuesday, June 3rd, 2014 at 18:46.
Filed under oracle
.
