Display all matched records using Oracle Query

I have query in Oracle using Connect by Level to display all matched records with regexp_substr but it doesn’t display all, I need to change the Connect by Level from +1 to +8 to get all the result. What if the matches are more than that in the future? I want to capture regardless how many matches without changing the +1 to +10 or +20, etc. Is there any easiest and fastest approach?

Here’s my query:

SELECT     DATA_SOURCE,REGEXP_SUBSTR(DATA_SOURCE,'A-S+|ABCS+',1, LEVEL) AS REF_NUM
FROM
(
SELECT '
Z-TEST
Y-TEST
A-123456789
ABC123456790
Y-TRY
A-123456791
ABC123456792
ABC123456793
Y-TRY
Y-TRY
Z-TEST
Y-TEST
Z-TEST
Y-TEST
A-123456794
ABC123456795
ABC123456796
Y-TRY
Z-TEST
Y-TEST
Z-TEST
Y-TEST
A-123456797
ABC123456798
' DATA_SOURCE
FROM DUAL
)
CONNECT BY LEVEL <= LENGTH(REGEXP_COUNT(DATA_SOURCE,'A-S+|ABCS+'))+1

Result: 
A-123456789
ABC123456790
A-123456791

Desired Result:
REF_NUM
A-123456789
ABC123456790
A-123456791
ABC123456792
ABC123456793
A-123456794
ABC123456795
ABC123456796
A-123456797
ABC123456798

Answer

Your connect by clause should be

CONNECT BY LEVEL <= REGEXP_COUNT(DATA_SOURCE,'A-S+|ABCS+', 1)

instead of

CONNECT BY LEVEL <= LENGTH(REGEXP_COUNT(DATA_SOURCE,'A-S+|ABCS+'))+1

But if you want to apply this solution to a table with more rows, you will need to add this :

CONNECT BY LEVEL <= REGEXP_COUNT(DATA_SOURCE,'A-S+|ABCS+', 1)
AND PRIOR DATA_SOURCE = DATA_SOURCE
AND PRIOR SYS_GUID() IS NOT NULL

to your connect by clause.

So your query should ultimately be this :

SELECT     --DATA_SOURCE, 
  REGEXP_SUBSTR(DATA_SOURCE,'A-S+|ABCS+',1, LEVEL) AS REF_NUM
FROM
(
SELECT '
Z-TEST
Y-TEST
A-123456789
ABC123456790
Y-TRY
A-123456791
ABC123456792
ABC123456793
Y-TRY
Y-TRY
Z-TEST
Y-TEST
Z-TEST
Y-TEST
A-123456794
ABC123456795
ABC123456796
Y-TRY
Z-TEST
Y-TEST
Z-TEST
Y-TEST
A-123456797
ABC123456798
' DATA_SOURCE
FROM DUAL
)
CONNECT BY LEVEL <= REGEXP_COUNT(DATA_SOURCE,'A-S+|ABCS+', 1)
AND PRIOR DATA_SOURCE = DATA_SOURCE
AND PRIOR SYS_GUID() IS NOT NULL
;

demo

For further explanation