Jul 23, 2018

REGEXP_SUBSTR: Create a list from a comma separated line

You can create a numbered list from a comma separated string line using REGEXP_SUBSTR function.

WITH t AS
   (SELECT 'PAYABLES, RECEIVABLES, GENERAL LEDGER, INVENTORY, PURCHASING'  str,
           '[^|,]+'  sep
      FROM dual)
 SELECT level, TRIM (REGEXP_SUBSTR (t.str, t.sep, 1, LEVEL)) parsed_str
   FROM t
CONNECT BY LEVEL <= REGEXP_COUNT (t.str, t.sep);


No comments: