Oracle 18c

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

create table your_table (data) as
select 'B-123
C-893-abcdef
D-def
Z-abc
B-123
C-891 zxcvbn
Y-def
A-abc
Q-123
C-892
H-def' from dual;
1 rows affected

select regexp_count(data, '^C-', 1, 'm') from your_table


REGEXP_COUNT(DATA,'^C-',1,'M')


3


select regexp_substr(data, '^C-(.+)$', 1, level, 'm') item_number,
  level as position_number
from your_table
connect by level <= regexp_count(data, '^C-', 1, 'm')


ITEM_NUMBER
POSITION_NUMBER


C-893-abcdef
1

C-891 zxcvbn
2

C-892
3


with rcte (data, lines, item_number, position_number) as (
  select data,
    regexp_count(data, '^C-', 1, 'm'),
    regexp_substr(data, '^C-(.+)$', 1, 1, 'm'),
    1
  from your_table
  union all
  select data,
    lines,
    regexp_substr(data, '^C-(.+)$', 1, r.position_number + 1, 'm'),
    position_number + 1
  from rcte r
  where r.position_number < r.lines
)
select *
from rcte


DATA
LINES
ITEM_NUMBER
POSITION_NUMBER


B-123
C-893-abcdef
D-def
Z-abc
B-123
C-891 zxcvbn
Y-def
A-abc
Q-123
C-892
H-def
3
C-893-abcdef
1

B-123
C-893-abcdef
D-def
Z-abc
B-123
C-891 zxcvbn
Y-def
A-abc
Q-123
C-892
H-def
3
C-891 zxcvbn
2

B-123
C-893-abcdef
D-def
Z-abc
B-123
C-891 zxcvbn
Y-def
A-abc
Q-123
C-892
H-def
3
C-892
3


with rcte (data, lines, item_number, position_number) as (
  select data,
    regexp_count(data, '^C-', 1, 'm'),
    regexp_substr(data, '^C-(.+)$', 1, 1, 'm'),
    1
  from your_table
  union all
  select data,
    lines,
    regexp_substr(data, '^C-(.+)$', 1, r.position_number + 1, 'm'),
    position_number + 1
  from rcte r
  where r.position_number < r.lines
)
select item_number, position_number
from rcte


ITEM_NUMBER
POSITION_NUMBER


C-893-abcdef
1

C-891 zxcvbn
2

C-892
3

DATA	LINES	ITEM_NUMBER	POSITION_NUMBER
B-123 C-893-abcdef D-def Z-abc B-123 C-891 zxcvbn Y-def A-abc Q-123 C-892 H-def	3	C-893-abcdef	1
B-123 C-893-abcdef D-def Z-abc B-123 C-891 zxcvbn Y-def A-abc Q-123 C-892 H-def	3	C-891 zxcvbn	2
B-123 C-893-abcdef D-def Z-abc B-123 C-891 zxcvbn Y-def A-abc Q-123 C-892 H-def	3	C-892	3