Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE some_table( some_id, value ) AS
  SELECT 8, 'A1,A2,A3;A4,A5,A6' FROM DUAL UNION ALL
  SELECT 8, 'B1,B2,B3;B4,B5,B6' FROM DUAL UNION ALL
  SELECT 8, 'C1,C2,C3;C4,C5,C6' FROM DUAL;
3 rows affected

WITH line_start_end ( some_id, value, startidx, endidx ) AS (
  SELECT some_id,
         value,
         1,
         INSTR( value, ';', 1 )
  FROM   some_table
  WHERE  some_id = 8
UNION ALL
  SELECT some_id,
         value,
         endidx + 1,
         INSTR( value, ';', endidx + 1 )
  FROM   line_start_end
  WHERE  endidx > 0
)
SELECT some_id,
       CASE
       WHEN endidx = 0
       THEN SUBSTR( value, startidx )
       ELSE SUBSTR( value, startidx, endidx - startidx )
       END AS value
FROM   line_start_end;


SOME_ID
VALUE


8
A1,A2,A3

8
B1,B2,B3

8
C1,C2,C3

8
A4,A5,A6

8
B4,B5,B6

8
C4,C5,C6


select regexp_substr(value, '[^;]+', 1, level),
       SYS_CONNECT_BY_PATH( regexp_substr(value, '[^;]+', 1, level), ';' )
from   some_table 
where  some_id = 8
connect by regexp_substr(value, '[^;]+', 1, level) is not null;


REGEXP_SUBSTR(VALUE,'[^;]+',1,LEVEL)
SYS_CONNECT_BY_PATH(REGEXP_SUBSTR(VALUE,'[^;]+',1,LEVEL),';')


A1,A2,A3
;A1,A2,A3

A4,A5,A6
;A1,A2,A3;A4,A5,A6

B4,B5,B6
;A1,A2,A3;B4,B5,B6

C4,C5,C6
;A1,A2,A3;C4,C5,C6

B1,B2,B3
;B1,B2,B3

A4,A5,A6
;B1,B2,B3;A4,A5,A6

B4,B5,B6
;B1,B2,B3;B4,B5,B6

C4,C5,C6
;B1,B2,B3;C4,C5,C6

C1,C2,C3
;C1,C2,C3

A4,A5,A6
;C1,C2,C3;A4,A5,A6

B4,B5,B6
;C1,C2,C3;B4,B5,B6

C4,C5,C6
;C1,C2,C3;C4,C5,C6

REGEXP_SUBSTR(VALUE,'[^;]+',1,LEVEL)	SYS_CONNECT_BY_PATH(REGEXP_SUBSTR(VALUE,'[^;]+',1,LEVEL),';')
A1,A2,A3	;A1,A2,A3
A4,A5,A6	;A1,A2,A3;A4,A5,A6
B4,B5,B6	;A1,A2,A3;B4,B5,B6
C4,C5,C6	;A1,A2,A3;C4,C5,C6
B1,B2,B3	;B1,B2,B3
A4,A5,A6	;B1,B2,B3;A4,A5,A6
B4,B5,B6	;B1,B2,B3;B4,B5,B6
C4,C5,C6	;B1,B2,B3;C4,C5,C6
C1,C2,C3	;C1,C2,C3
A4,A5,A6	;C1,C2,C3;A4,A5,A6
B4,B5,B6	;C1,C2,C3;B4,B5,B6
C4,C5,C6	;C1,C2,C3;C4,C5,C6