I Have string How use split in pl/sql?

Question

I Have string character and I Should split this string with Comma

Can anyone help me ?

give example input, example output, the code you already tried, and explain what exactly goes wrong with that code. — hoijui, Jun 22 '15 at 06:56
my input (name,family,age,...,) and output name next line family next line age and any more — ashkan, Jun 22 '15 at 06:59

score 1 · Answer 1 · answered Jun 22 '15 at 07:13

Oracle 11g R2 Schema Setup:

CREATE TABLE data ( id, str ) AS 
SELECT 1, '1,2,3,4,5' FROM DUAL
UNION ALL SELECT 2, 'One,Two,Three' FROM DUAL
UNION ALL SELECT 3, 'AA,BB,CC,DD,,EE' FROM DUAL;

Query 1:

SELECT  ID,
        REGEXP_SUBSTR( d.str, '[^,]+', 1, l.COLUMN_VALUE ) AS value,
        l.COLUMN_VALUE AS "index"
FROM    data d,
        TABLE(
          CAST(
            MULTISET(
              SELECT LEVEL
              FROM   DUAL
              CONNECT BY  LEVEL <= REGEXP_COUNT( d.str, '[^,]+')
            ) AS sys.OdciNumberList
          )
        ) l
ORDER BY
       1,3

Results:

| ID | VALUE | index |
|----|-------|-------|
|  1 |     1 |     1 |
|  1 |     2 |     2 |
|  1 |     3 |     3 |
|  1 |     4 |     4 |
|  1 |     5 |     5 |
|  2 |   One |     1 |
|  2 |   Two |     2 |
|  2 | Three |     3 |
|  3 |    AA |     1 |
|  3 |    BB |     2 |
|  3 |    CC |     3 |
|  3 |    DD |     4 |
|  3 |    EE |     5 |

I Have string How use split in pl/sql?

1 Answers1