Make a query to split a line into x line depending on a field

Question

I got a line in my data base, containing a fields with some names, separated by a ; .

I am trying to get, with a Select query, as many lines, that there is name in my field.

So, if I got in my DB :

id names
1  john;jack;mike

With this, i must get with this query :

SELECT id, split(names, ';') from table 

1 john
1 jack
1 mike

I don't have any other table, so no join.

I write : split(names, ';'), but this is just an example of what I want to do.

But I don't know how to split my field names to do it like ahead.

I don't found any thing to help me, find a way to do it.

Do I need to use a function? Is there a function to do so with oracle?

Thank you.

possible duplicate of [Oracle 10g Splitting string into multiple rows](http://stackoverflow.com/questions/14328621/oracle-10g-splitting-string-into-multiple-rows) — Peter Lang, Mar 27 '14 at 13:21
@PeterLang Almost, but the answer in this other questions does not work for me, while the answer here by BhupeshC fit perfectly, thanks to the prior it seems. The the edit linking a question with uncorrect answer is not really relevant. — provençal le breton, Mar 27 '14 at 13:28

score 1 · Accepted Answer · answered Mar 27 '14 at 13:24

1

SELECT id, TRIM(REGEXP_SUBSTR( names, '[^;]+', 1, LEVEL)) FROM table_name
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(names, '[^;]+')) + 1
AND PRIOR id = id
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL

SQL Fiddle

answered Mar 27 '14 at 13:24

KrazzyNefarious

3,202
3
20
32

It is perfect. Can you add please, some explanations for the two last lines, with the `PRIOR` key word? – provençal le breton Mar 27 '14 at 13:30

Make a query to split a line into x line depending on a field

1 Answers1