There are four strings separated into two parts by vertical bar, namely: ID|DUE_DATE, 80781|2026-12-01, VARCHAR2|DATE, |yyyy-mm-dd. Now we need to generate a four-field result set, extract the first part of each string and write it as the first record, and then extract the second part of each string and write it as the second record.

It can be implemented with the help of databases such as Oracle, but SQL only has a small number of basic string functions and lacks set related functions, making it difficult to split strings into batch records. Although Oracle SQL also supports powerful regular expressions, it is not very useful here. The set of SQL is unordered, and it is not convenient to retrieve set members by position. It also requires the use of ROW_NUMBER or CONNECT BY to indirectly implement, which is very cumbersome in code.

SPL has many string processing functions related to sets, which can intuitively and conveniently parse strings into batch records. SPL also supports ordered sets, which can directly retrieve set members by position and write code according to the logic of the task.

A1: Parse parameters into a set of sets. The split function can parse a string into a set of strings using a specified delimiter. @n means to split it by the carriage return \n first, and then by the specified delimiter. The parameter argStr is a string concatenated with carriage returns, ID|DUE_DATE\n80781|2026-12-01\nVARCHAR2|DATE\n|yyyy-mm-dd.

A2: Create a new empty result set.

A3: Retrieve the first sub member of each member in A1, then retrieve the second sub member of each member, merge them into a one-dimensional set, and write them into the new result set in sequence. If there are many records, you can also loop to retrieve data: A1(1).len().conj(t=~,A1.(~(t)))

Question source：https://stackoverflow.com/questions/78340545/split-string-using-regexp-substr-in-oracle-sql-gone-wrong