Oracle 18c

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

CREATE TABLE my_data_source
    (id int, main_txt varchar2(29))
;

INSERT ALL 
    INTO my_data_source (id, main_txt)
         VALUES (1, 'wpfwe abc weiofnew abc wieone')
    INTO my_data_source (id, main_txt)
         VALUES (2, 'abc weoin')
    INTO my_data_source (id, main_txt)
         VALUES (4, 'abc weoifn abc we abc w')
    INTO my_data_source (id, main_txt)
         VALUES (3, 'weoifn abcwe')
SELECT * FROM dual
4 rows affected

WITH match_counts(id, match_count) AS (
  SELECT DISTINCT id, regexp_count(main_txt, 'abc \S+', 1)
  FROM my_data_source
  WHERE regexp_count(main_txt, 'abc (\S+)', 1) > 0
),
match_nums(id, match_num, match_count) AS (
  SELECT id, 1, match_count
  FROM match_counts
  UNION ALL
  SELECT id, match_num + 1, match_count
  FROM match_nums
  WHERE match_num < match_count
)
SELECT m.id, main_txt, regexp_substr(main_txt, 'abc (\S+)', 1, match_num) AS output
FROM my_data_source m
JOIN match_nums n ON m.id = n.id
ORDER BY m.id, n.match_num


ID
MAIN_TXT
OUTPUT


1
wpfwe abc weiofnew abc wieone
abc weiofnew

1
wpfwe abc weiofnew abc wieone
abc wieone

2
abc weoin
abc weoin

4
abc weoifn abc we abc w
abc weoifn

4
abc weoifn abc we abc w
abc we

4
abc weoifn abc we abc w
abc w

ID	MAIN_TXT	OUTPUT
1	wpfwe abc weiofnew abc wieone	abc weiofnew
1	wpfwe abc weiofnew abc wieone	abc wieone
2	abc weoin	abc weoin
4	abc weoifn abc we abc w	abc weoifn
4	abc weoifn abc we abc w	abc we
4	abc weoifn abc we abc w	abc w