Split Comma Separated Values Using Oracle Function

There are number of ways to split comma separated values. This is very simple method to separate the comma separated or any other separated string. you can change the delimiter as per your requirement. This method have a limitation that string can not exceed the 4000 characters.

if you want  a functionality more than 4000 characters, Please go for long input values also use packages instead of procedures, because functions do not support long parameter type.

Please comment for any issues/Questions.

 

Code Snippet

1. CREATE OR REPLACE FUNCTION FNC_SPLIT( p_list varchar2, p_del varchar2 := ',') return tp_split_tbl pipelined
2.     is
3.         l_idx    pls_integer;
4.         l_list    varchar2(32767) := p_list;
5.         
6.     begin
7.         loop
8.             l_idx := instr(l_list,p_del);
9.             if l_idx > 0 then
10.                 pipe row(substr(l_list,1,l_idx-1));
11.                 l_list := substr(l_list,l_idx+length(p_del));
12.  
13.             else
14.                 pipe row(l_list);
15.                 exit;
16.             end if;
17.         end loop;
18.         return;
19.     end ;

 

How to call function:

 

Code Snippet

1. SELECT NVL(COLUMN_VALUE, '') emp_name
2.     FROM TABLE(FNC_SPLIT(comma_separated_string, ','))

The above function returns the pipeline table, you can use a temporary table to insert the values.