Selecting all values from multivalued field in Oracle c11 - SQL query

Question

I am trying to select all the values from multivalued data column. Values are separated with ý.

This query I wrote does exactly what I intended, but unfortunately I am working on 11g so I can't use CROSS APPLY. Any suggestions how to go around CROSS APPLY? Or any other ideas?

select REGEXP_SUBSTR (MFIELD, '([^ý]+)',1,l.lvl,NULL) AS item
FROM TABLE
CROSS APPLY
(
SELECT LEVEL AS lvl
         FROM   DUAL
         CONNECT BY LEVEL <= REGEXP_COUNT( MFIELD, 'ý' ) 
)l;

Answer 1

Switch to

SQL> WITH
  2     my_table (mfield)
  3     AS
  4        (SELECT 'abcýdef' FROM DUAL
  5         UNION ALL
  6         SELECT 'xyzýmnoýzzz' FROM DUAL
  7         UNION ALL
  8         SELECT 'ý18524ý2879' FROM DUAL)
  9  SELECT mfield,
 10         REGEXP_SUBSTR (mfield,
 11                        '[^ý]+',
 12                        1,
 13                        COLUMN_VALUE) AS item
 14    FROM my_table
 15         CROSS JOIN
 16         TABLE (
 17            CAST (
 18               MULTISET (
 19                      SELECT LEVEL
 20                        FROM DUAL
 21                  CONNECT BY LEVEL <=
 22                             REGEXP_COUNT (LTRIM (mfield, 'ý'), 'ý') + 1)
 23                  AS SYS.odcinumberlist));

MFIELD      ITEM
----------- -----------
abcýdef     abc
abcýdef     def
xyzýmnoýzzz xyz
xyzýmnoýzzz mno
xyzýmnoýzzz zzz
ý18524ý2879 18524
ý18524ý2879 2879

7 rows selected.

SQL>