Convert comma separated string to array in PL/SQL

Question

How do I convert a comma separated string to a array?

I have the input '1,2,3' , and I need to convert it into an array.

Answer 1

You can use Replace Function to replace comma easily. To Do this-

The syntax for the REPLACE function in SQL Server (Transact-SQL) is:
REPLACE( string, string_to_replace, replacement_string )

Parameters or Arguments

string : The source string from which a sequence of characters will be replaced by another set of characters.
string_to_replace : The string that will be searched for in string1.
replacement_string : The replacement string. All occurrences of string_to_replace will be replaced with replacement_string in string1.
Note :

The REPLACE function performs a replacement that is not case-sensitive. So all occurrences of string_to_replace will be replaced with replacement_string regardless of the case of string_to_replace or replacement_string

For Example :
SELECT REPLACE('Kapil,raj,chouhan', ',' , ' ') from DUAL;
Result : Kapil raj chouhan

SELECT REPLACE('I Live In India', ' ' , '-') from DUAL;
Result : I-Live-In-India

SELECT REPLACE('facebook.com', 'face' , 'friends') from DUAL;
Result : friendsbook.com

I Hope it will be usefull for you.

Answer 2

A quick search on my BBDD took me to a function called split:

create or replace function split
( 
p_list varchar2, 
p_del varchar2 := ','
) 
return split_tbl pipelined
is 
l_idx pls_integer; 
l_list varchar2(32767) := p_list;AA 
l_value varchar2(32767);
begin 
loop 
l_idx := instr(l_list,p_del); 
if l_idx > 0 then 
pipe row(substr(l_list,1,l_idx-1)); 
l_list := substr(l_list,l_idx+length(p_del));
else 
pipe row(l_list); 
exit; 
end if; 
end loop; 
return;
end split;

I don't know if it'll be of use, but we found it here...