create or replace function str_split(the_list varchar2,the_index number,delim varchar2 := ',')
return varchar2
is
start_pos number;
end_pos number;
begin
if the_index = 1 then
start_pos := 1;
else
start_pos := instr(the_list, delim, 1, the_index - 1);
if start_pos = 0 then
return null;
else
start_pos := start_pos + length(delim);
end if;
end if;
end_pos := instr(the_list, delim, start_pos, 1);
if end_pos = 0 then
return substr(the_list, start_pos);
else
return substr(the_list, start_pos, end_pos - start_pos);
end if;
end str_split;
/
--Create a TABLE Collection Object
ReplyDeleteCREATE TYPE tbl_array AS table OF VARCHAR2(32000);
/
--Create a SPLIT Fucntion which return Table Collection
CREATE OR REPLACE FUNCTION SPLIT(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',')
RETURN tbl_array PIPELINED PARALLEL_ENABLE
AS
v_cnt NUMBER ;
idx NUMBER ;
v_string VARCHAR2(32000);
v_start NUMBER := 0;
v_end NUMBER := 0;
BEGIN
-- Get Number of occurrences
v_cnt := LENGTH(p_string) - LENGTH(REPLACE(p_string,p_delimiter,'')) ;
FOR idx IN 1..v_cnt LOOP
v_end := INSTR(p_string,p_delimiter,1, idx);
v_string := SUBSTR (p_string, v_start + 1 , v_end - v_start - 1);
v_start := v_end ;
PIPE ROW(TO_CHAR(v_string));
END LOOP;
--Last split
v_string := SUBSTR (p_string, - (LENGTH(p_string) - v_end));
PIPE ROW(TRIM(v_string));
RETURN;
END SPLIT
/
--Testing
SELECT * FROM TABLE(SPLIT('Sriniavs,Sreeramoju,New York,USA'));
/