Oracle: SPLIT Function

There is NO direct SPLIT function Exists in Oracle as of today. You have to create your own custom split function in order to achieve your goal.
There are many ways you could create your own custom SPLIT function depending on your requirement.
An advantage of returning a TABLE Collection object is you could directly reference the function in your SQL query as a TABLE.


Following example illustrate a simple customized SPLIT function which returns TABLE Collection object:

--Create a TABLE Collection Object
CREATE 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'));
/

Another example illustrates a simple customized SPLIT query:

WITH tbl_split AS
(SELECT 'Sriniavs,Sreeramoju,New York,USA' val from dual )
SELECT TO_CHAR( SUBSTR (val, (DECODE (LEVEL, 1, 0, INSTR (val, ',', 1, LEVEL - 1)) + 1),
(DECODE (INSTR (val, ',', 1, LEVEL) - 1,-1, LENGTH (val),INSTR (val, ',', 1, LEVEL) - 1))
- (DECODE (LEVEL, 1, 0, INSTR (val, ',', 1, LEVEL - 1)) + 1)+ 1
)) a
FROM tbl_split
CONNECT BY LEVEL <=
(SELECT (LENGTH (val) - LENGTH (REPLACE (val, ',', NULL)))
FROM tbl_split) + 1


/
Another Example :

create or replace type myTableType as table of Varchar2(255);


create or replace function str2tbl
      (p_str in varchar2,
       p_delim in varchar2 default '.')    return myTableType
as
l_str  long default p_str || p_delim;
l_n number;
l_data myTableType := myTabletype();

begin
loop
     l_n := instr( l_str, p_delim );
     exit when (nvl(l_n,0) = 0);
     l_data.extend;
     l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
     l_str := substr( l_str, l_n+length(p_delim) );
end loop;

return l_data;
end;

SELECT * FROM TABLE (CAST (str2tbl ('10.01.03.04.234') AS mytabletype));