First of all you need to create a varchar2 array type. I've found it very useful to create a generic varchar2 array I can use anywhere.
CREATE TYPE "VARCHAR2_ARRAY" AS TABLE OF VARCHAR2(32767)
Now the function...CREATE OR REPLACE FUNCTION string_to_array
( p_string IN VARCHAR2,
p_seperator IN VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2_ARRAY DETERMINISTIC IS
l_string VARCHAR2(4000) := p_seperator||p_string||p_seperator;
l_array VARCHAR2_ARRAY := VARCHAR2_ARRAY();
BEGIN
IF LENGTH(p_seperator) != 1 THEN
RAISE_APPLICATION_ERROR(-20101,'Seperator should only be one character');
END IF;
SELECT SUBSTR(l_string, INSTR(l_string,p_seperator,1,LEVEL)+1,
INSTR(l_string,p_seperator,1,LEVEL+1) - INSTR(l_string,p_seperator,1,LEVEL)-1 ) token
BULK COLLECT INTO l_array
FROM DUAL
CONNECT BY LEVEL < LENGTH(l_string)-LENGTH(REPLACE(l_string,p_seperator,''));
RETURN(l_array);
END;
/
String Split...Done.
Posts
