Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

7 rows affected

WITH terms ( value, terms, num_terms ) AS (
  SELECT SUBSTR( value, 1, INSTR( value, ', ', -1 ) - 1 ),
         SUBSTR( value, INSTR( value, ', ', -1 ) + 2 ),
         REGEXP_COUNT(
           SUBSTR( value, INSTR( value, ', ', -1 ) + 2 ),
           '.+?(\| |$)'
         )
  FROM   table_name
),
term_bounds ( rn, value, terms, start_pos, lvl ) AS (
  SELECT ROWNUM,
         REPLACE(
           value,
           '%' || num_terms,
           CASE num_terms
           WHEN 1
           THEN terms
           ELSE SUBSTR( terms, INSTR( terms, '| ', 1, num_terms - 1 ) + 2 )
           END
         ),
         terms,
         CASE
         WHEN num_terms > 1
         THEN INSTR( terms, '| ', 1, num_terms - 1 )
         ELSE 1
         END,
         num_terms
  FROM   terms
UNION ALL
  SELECT rn,
         REPLACE(
           value,
           '%' || (lvl - 1),
           CASE lvl - 1
           WHEN 1
           THEN SUBSTR( terms, 1, start_pos - 1 )
           ELSE SUBSTR(
                  terms,
                  INSTR( terms, '| ', 1, lvl - 2 ) + 2,
                  start_pos - INSTR( terms, '| ', 1, lvl - 2 ) - 2
                )
           END
         ),
         terms,
         CASE
         WHEN lvl > 2
         THEN INSTR( terms, '| ', 1, lvl - 2 )
         ELSE 1
         END,
         lvl - 1
  FROM   term_bounds
  WHERE  lvl > 1
)
SEARCH DEPTH FIRST BY rn SET rn_order
SELECT value
FROM   term_bounds
WHERE  lvl = 1;

VALUE
SITE_NO\|\|'-'\|\|SITE_NAME\|\|COUNTRY
0.1 * WIND_RES
TOTAL
CASE WHEN LENGTH(MIN_DATE) < 8 THEN NULL ELSE TO_DATE(MIN_DATE,'yyyymmdd')END
ABC(+)=PQR and LMN(+)=XYZ
ONE, TWO, THREE, FOUR, FIVE, SIX, SEVEN, EIGHT, NINE, TEN, ELEVEN
HELLO