Two ways using string to table in APEX selects

XML

Two ways using string to table in APEX selects

Von Tobias Arnhold → 5.03.2011

First way
This example uses "REGULAR expressions" and the "connect by" clause. Perfect when you select on columns including lists.
Forum entry: http://forums.oracle.com/forums/thread.jspa?messageID=9494074

WITH TABLE1
       AS (SELECT 1 my_id, '8092:7054:9237:4232:3333:4023:6781' my_list FROM DUAL
           UNION
           SELECT 2, '8765:2231:2242:3412:3453' FROM DUAL
           UNION
           SELECT 3, '2121' FROM DUAL
           UNION
           SELECT 4, '6565:9121' FROM DUAL)
  SELECT my_id,REGEXP_SUBSTR ( my_list, '([^:]+)', 1, lvl)
    FROM TABLE1,
         (SELECT LEVEL lvl
            FROM (SELECT MAX (LENGTH (REGEXP_REPLACE ( my_list || ':', '[^:]'))) mx
                    FROM TABLE1)
          CONNECT BY LEVEL <= mx + 1)
   WHERE lvl - 1 <= LENGTH (REGEXP_REPLACE ( my_list || ':', '[^:]'))
     AND REGEXP_SUBSTR ( my_list, '([^:]+)', 1, lvl) IS NOT NULL
   ORDER BY my_id,lvl;

Second way
This example is based on a nice XML solution. Only needs two lines of code. Easy to use in combination with variables.
Forum entry; http://forums.oracle.com/forums/thread.jspa?threadID=2184251&tstart=0&messageID=9406487

:F_STRING := '8092:7054:9237:4232:3333:4023:6781'

select upper(extractvalue(column_value,'e'))
from table(xmlsequence(xmltype('' || replace(:F_STRING,':','') || '').extract('e/*')));