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
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
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('').extract('e/*'))); ' || replace(:F_STRING,':',' ') || '
1 One Comment " Two ways using string to table in APEX selects "
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,
Post a Comment