APEX-AT-WORK no image

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

1 One Comment " Two ways using string to table in APEX selects "

dissertation methodology help 29 January, 2013 16:00

Appropriate a great way of Being Introduced to this topic Two ways using string to table in APEX selects matter. It was great catching up is this? Thank you gently for the conclusion,