Tuesday 9 October 2012

Oracle Function to Split String into an Array

This deterministic function creates a general function for converting a comma seperated string into an array.

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.