Oracle splits strings to show in single-row multiline

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 results are as follows:

Field2

A
Bbb
Cccc
Ddddd
Ee
D
A

Connect by: Recursion, which is the condition for the query to continue

InStr (field1, ' | ', 2, rownum) string starting from the 2nd position, starting from a, ' | ' 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 is, there will be seven rows.

The second parameter of the SUBSTR function is InStr (field1, ' | ', 1, rownum) + 1, which means ' | ' Start match from 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 take a closer look, constant equals one.

In other words, press ' | ' After the division, the first take the second part, the second take the four parts, and so on.

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

Because RowNum is incremented, it becomes seven rows of data.

The analysis is complete.

Oracle splits strings to show in single-row multiline