Split function in oracle to comma separated values with automatic sequence

Question

Need Split function which will take two parameters, string to split and delimiter to split the string and return a table with columns Id and Data.And how to call Split function which will return a table with columns Id and Data. Id column will contain sequence and data column will contain data of the string. Eg.

SELECT*FROM Split('A,B,C,D',',')

Result Should be in below format:

|Id | Data
 --   ----
|1  | A  |
|2  | B  |
|3  | C  |
|4  | D  |

Answer 1

Here is how you could create such a table:

 SELECT LEVEL AS id, REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) AS data
   FROM dual
CONNECT BY REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) IS NOT NULL;

With a little bit of tweaking (i.e., replacing the , in [^,] with a variable) you could write such a function to return a table.