After the oracle splits the string, it is displayed in a single column and multiple rows in oracle.

After the oracle splits the string, it is displayed in a single column and multiple rows in oracle.

Original SQL:

Select substr (field1, instr (field1, '|', 1, rownum) + 1, instr (field1, '|', 1, rownum + 1)-instr (field1, '|', 1, rownum)-1) as field2 from (select '| 'a | bbb | cccc | ddddd | ee | d | a' |' as field1 from dual) connect by instr (field1, '|', 2, rownum)> 0;

The query result is as follows:

Field2

A
Bbb
Cccc
Ddddd
Ee
D
A

Connect by: recursion, that is, the condition for continued Query

The instr (field1, '|', 2, rownum) string starts from the second position, that is, from a, and the '|' separator appears from the first time to the eighth time in sequence: 3, 5, 7, 9, 11, 13, 15, 0, so there will be seven queries, that is, there will be seven rows.

The second parameter of the substr function is instr (field1, '|', 1, rownum) + 1, indicating '|' matching starts from the first position, add the first to the seventh matching position, that is, 2, 4, 6, 8, 10, 12, and 14.

The third parameter of the substr function is always equal to 1.

That is to say, after dividing by '|', take the second part for the first time, take the fourth part for the second time, and so on.

The values are as follows: a, bbb, cccc, ddddd, ee, d, and.

Because rownum is incremental, it turns into seven rows of data.

The analysis is complete.