Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

convert comma separated string into rows

726472Oct 14 2010 — edited Oct 15 2013
Hi
I am looking to convert this comma separated string 1,2,3,4,5,6
into
Col1
-------
1
2
3
4
5
6

using single sql query, no plsql block or object creation, i try this sql but it appear error, can any one help me?
SELECT EXTRACT (VALUE (d), '//row/text()').getstringval () AS DATA
  FROM (SELECT XMLTYPE (   '<rows><row>'
                        || REPLACE ('1,2,3,4,5,6', ',', '</row><row>')
                        || '</row></rows>'
                       ) AS xmlval
          FROM DUAL) x,
       TABLE (XMLSEQUENCE (EXTRACT (x.xmlval, '/rows/row'))) d;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 12 2013
Added on Oct 14 2010
8 comments
51,672 views