<div>
Excellent. didnt need the temporary table - just the algorithm.
</div>

<div>
even simpler, in PL/SQL, there is a utility package: DBMS_UTILITY.comma_to_table() 
can convert comma-delimited string into a PL/SQL table in one function call. 
 
for example: 
 
DBMS_UTILITY.comma_to_table('&lt;string&gt;', table_length, &lt;pl/sql_tabname&gt;); 
 

</div>

<div>
Here is an example of seazodiac suggestion 
 
CREATE OR REPLACE PROCEDURE COMMA_SAMPLE AS &nbsp; 
mytable DBMS_UTILITY.uncl_array; &nbsp; 
mylist VARCHAR2(80); &nbsp; 
mytable_count NUMBER; 
BEGIN &nbsp; 
&nbsp; &nbsp;mylist := '1,2,3,4,5'; 
&nbsp; &nbsp;DBMS_UTILITY.COMMA_TO_TABLE(mylist, mytable_count, mytable); 
&nbsp; &nbsp;mylist := 'Empty.'; 
&nbsp; &nbsp;DBMS_OUTPUT.PUT_LINE('MYTABLE: '); &nbsp; 
&nbsp; &nbsp;DBMS_OUTPUT.PUT_LINE('---------------------------'); &nbsp; 
&nbsp; &nbsp;FOR item IN 1..mytable_count LOOP &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp; DBMS_OUTPUT.PUT_LINE(mytable(item)); &nbsp; 
&nbsp; &nbsp;END LOOP; &nbsp; DBMS_OUTPUT.PUT_LINE(''); &nbsp; 
END; 
&nbsp;/ 
</div>

<div>
 
&nbsp;One bug I've had with dbms_utility.comma_to_table is that when you have data that starts with number, it fails, works fine if data has alpha character as first symbol (the same as with identifiers in Oracle), So, for example 
 
mylist := '1,2,3,4,5'; 
&nbsp; &nbsp;DBMS_UTILITY.COMMA_TO_TABLE(mylist, mytable_count, mytable); 
 
will fail where 
 
mylist := 'a1,a2,a3,a4,a5'; 
&nbsp; &nbsp;DBMS_UTILITY.COMMA_TO_TABLE(mylist, mytable_count, mytable); 
 
Worked OK. The workaround was to add alpha character in app that passed the list (VB) and unparse it in Oracle PL/SQL. It was with 9i (9.2.01 I believe )
</div>

<div>
Pontis is correct. Number character does cause the problem. 
 
but this can be solved easily by doing &quot;prefixing it with a letter and replacing it later&quot; 
 
for example: if you have a string of &quot;1,2,3,4,5&quot; 
you can prefix the digits with a fixed letter to make it look like this: 'a1,a2,a3,a4,a5' 
 
then do : 
DBMS_UTILITY.COMMA_TO_TABLE(mylist, mytable_count, mytable); 
 
then when you use it , you do &quot;replace(mytable(i), 'a', '')&quot; function to get rid of 'a'. 
 
that's all you need to do .. 
 
 
 

</div>

<div>
<div class="content wysiwyg-content">
Hi, 
Is there a way of selecting from a delimited list in SQL. 
 
for instance if i have a string eg '1,2,3,4,5' is there a way from employing SQL to give me 5 rows 
1 
2 
3 
4 
5 
? 
the reason is that i have a variable with this data in and want to use dynamic sql to loop through this data.
</div>
</div>

Hi,
Is there a way of selecting from a delimited list in SQL.

for instance if i have a string eg '1,2,3,4,5' is there a way from employing SQL to give me 5 rows
1
2
3
4
5
?
the reason is that i have a variable with this data in and want to use dynamic sql to loop through this data.

selecting from a comma delimited list

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.