Two ways using string to table in APEX selects

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/*')));

Share This:

Post Tags:

APEX examples XML