0

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;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
StanTheThey
  • 1
  • 1

1 Answers1

0

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>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Am getting an error ``` ORA-01788: CONNECT BY clause required in this query block 01788. 00000 - "CONNECT BY clause required in this query block" *Cause: *Action: Error at Line: 6 Column: 8 ``` Any idea why? – StanTheThey Apr 13 '22 at 06:26
  • Ah, yes - sorry, didn't pay attention to regular expressions. Should've been `column_value`. Have a look at edited code (which now contains test case as well). – Littlefoot Apr 13 '22 at 06:31
  • Thanks so much. I noticed that it sometimes return null though. e.g. ý18524ý2879 | 18524 ; ý18524ý2879 | 2879 ; ý18524ý2879 | (null) ; – StanTheThey Apr 13 '22 at 06:46
  • That's because of delimiter at the beginning of the string. Modify REGEXP_COUNT to `REGEXP_COUNT (LTRIM (mfield, 'ý'), 'ý') + 1)` – Littlefoot Apr 13 '22 at 06:53
  • You are absolutely wonderful. Thank you so much. Am wrinkling my brain for hours on this one. – StanTheThey Apr 13 '22 at 07:09
  • You're welcome, I'm glad if it helped. – Littlefoot Apr 13 '22 at 07:13