8 Replies Latest reply on Oct 15, 2013 7:31 AM by 236f2a50-9fb9-4782-a643-3c80c2b10114

    convert comma separated string into rows

    726472
      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;