Tuesday, March 2, 2010

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));







2 comments:

loquin said...
This comment has been removed by the author.
loquin said...

Thanks for posting this split function, but I needed a few enhancements, so I made a few mods.

First, it is common for deliminators to be more than 1 character in length. Windows new line character pairs immediately come to mind...

Second, I wanted to be able to get the field count from the function, and third, I wanted to be able to specify an individual field to retrieve, without using rownum in the calling function. The code below allows the use of multi-character field delimiters, and uses a third (optional) parameter to support both specific field selection, and field count:


--Create a TABLE Collection Object
CREATE TYPE tbl_array AS table OF VARCHAR2(32000);
/
--Create a SPLIT Fucntion which return Table Collection
CREATE FUNCTION PROD10.SPLIT(p_string IN VARCHAR2, p_delimiter IN VARCHAR2 := ',', p_item IN NUMBER := 0)
RETURN tbl_array PIPELINED PARALLEL_ENABLE
AS
-- Usage:
-- Return all fields in the string:
-- SELECT * FROM TABLE (split ('10.01.03.04.234', '.')); returns 5 records containing '10', '01', '03', '04', '234'
-- Return a specific field in the string:
-- SELECT * FROM TABLE (split ('10.01.03.04.234', '.', 5)) AS Field5; returns one record, containing '234'
-- Return the count of fields in the string:
-- SELECT * FROM TABLE (split ('10.01.03.04.234', '.', -1)) AS FIELD_COUNT; returns '5'
--
v_cnt NUMBER ;
idx NUMBER ;
v_string VARCHAR2(32000);
v_start NUMBER := 1;
v_end NUMBER := 0;

BEGIN

-- Get Number of DELIMITER occurrences
v_cnt := (LENGTH(p_string) - LENGTH(REPLACE(p_string,p_delimiter,'')))/Length(p_delimiter);
IF p_item < 0 THEN
PIPE ROW(TO_CHAR(v_cnt + 1));
ELSIF (p_item > v_cnt + 1) THEN
PIPE ROW (Null);
ELSE
FOR idx IN 1..v_cnt LOOP
--v_end := INSTR(p_string,p_delimiter,1, idx)
v_end := INSTR(p_string,p_delimiter, 1, idx);
v_string := SUBSTR (p_string, v_start , v_end - v_start);
v_start := v_end + Length(p_delimiter) ;
IF (p_item = 0 OR p_item = idx) THEN
PIPE ROW(TO_CHAR(v_string));
END IF;
END LOOP;
--Last split (if necessary)
If p_item = 0 or p_item = v_cnt + 1 then
v_start := v_end + Length(p_delimiter);
v_string := SUBSTR (p_string, v_start);
PIPE ROW(TRIM(v_string));
END IF;
END IF;

RETURN;

END SPLIT;
/

Post a Comment

Post a Comment