Oracle cuts strings after a single row of multiple lines

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;

Query results such as the following:

Field2

A
Bbb
Cccc
Ddddd
Ee
D
A

Connect by: Recursive. The condition that the query continues

InStr (field1, ' | ', 2, RowNum) string starts from the 2nd position. From a start, ' | ' The delimiter from the first to the eighth appears in order: 3, 5, 7, 9, 11, 13, 15, 0, so it will be queried seven times. That means there will be seven rows.

The second parameter of the SUBSTR function is InStr (field1, ' | ', 1, rownum) + 1. means ' | ' Start matching from the first position. First to seventh match position plus one, i.e. 2, 4, 6, 8, 10, 12, 14

The third parameter of the SUBSTR function is a careful look, constant equals one.

In other words, press ' | ' After cutting, take the second part for the first time and take part four for the second time. In turn.

The values were: A, BBB, CCCC, DDDDD, EE, D, a

Since rownum is incremented, it becomes seven rows of data.

Analysis is complete.

Oracle cuts strings after a single row of multiple lines