Splitting Debug-Like Strings into Rows Instead of Columns

When the number of extracted segments isn’t fixed, unpivoting into rows gives flexibility. Instead of separate columns, each part becomes a row.

SELECT TRIM(SUBSTR(REGEXP_REPLACE('INV#8674767JSDEPOSIT DBCD PO DPO12345678 ASASADSDSS', '#|PO', ''),
                   REGEXP_INSTR('INV#8674767JSDEPOSIT DBCD PO DPO12345678 ASASADSDSS', 'PO'),
                   INSTR('INV#8674767JSDEPOSIT DBCD PO DPO12345678 ASASADSDSS',' ',1,1)-1 -
                   REGEXP_INSTR('INV#8674767JSDEPOSIT DBCD PO DPO12345678 ASASADSDSS','PO')-1)) AS segment
FROM dual
UNION ALL
SELECT TRIM(SUBSTR(REGEXP_REPLACE('INV#8674767JSDEPOSIT DBCD PO DPO12345678 ASASADSDSS','#|PO',''),
                   REGEXP_INSTR('INV#8674767JSDEPOSIT DBCD PO DPO12345678 ASASADSDSS','PO'),
                   INSTR('INV#8674767JSDEPOSIT DBCD PO DPO12345678 ASASADSDSS',' ',1,2)-1 -
                   REGEXP_INSTR('INV#8674767JSDEPOSIT DBCD PO DPO12345678 ASASADSDSS','PO')-1))
FROM dual;

This way, each part of the string appears in a separate row.