Split Procedure (Token) Fn

CREATE OR REPLACE FUNCTION get_new(
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 get_new;
/
*************************************************************************************
*************************************************************************************
-- The pl/sql Oracle version for the Tokenizer procedure
Create or Replace
procedure tokenizer ( iStart IN NUMBER,
sPattern in VARCHAR2,
sBuffer in VARCHAR2,
sResult OUT VARCHAR2,
iNextPos OUT NUMBER)
AS
nPos1 number;
nPos2 number;
BEGIN
nPos1 := Instr (sBuffer ,sPattern ,iStart);
IF nPos1 = 0 then
sResult := NULL ;
ELSE
nPos2 := Instr (sBuffer ,sPattern ,nPos1 + 1);
IF nPos2 = 0 then
sResult := Rtrim(Ltrim(Substr(sBuffer ,nPos1+1)));
iNextPos := nPos2;
else
sResult := Substr(sBuffer ,nPos1 + 1 , nPos2 - nPos1 - 1);
iNextPos := nPos2;
END IF;
END IF;
END tokenizer ;
/

-- a Procedure to Test the Tokenizer
Create or Replace procedure sp_test_tokenizer
as
sepr varchar2(1);
sbuf varchar2(200);
sres varchar2(200);
pos number;
istart number;
begin

sbuf := '@0@11@222@3333@44444@555555@6666666@77777777@888888888';
sepr := '@';
istart := 1;
tokenizer (istart ,sepr,sbuf,sres,pos);
if (pos <> 0) then
dbms_output.put_line (sres);
end if;
while (pos <> 0)
loop
istart := pos;
tokenizer (istart ,sepr,sbuf,sres,pos );
dbms_output.put_line (sres);
end loop;
END sp_test_tokenizer;
/

-- Test the Procedure From sqlPlus
set serveroutput on
exec sp_test_tokenizer;