Display all matched records using Oracle Query-CodePudding

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 |ABC\S ',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 |ABC\S ')) 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

CodePudding user response：

Change connect by condition to e.g. number of CHR(13) appearances (line #35), and then select rows whose ref_num exists (line #38). Something like this:

SQL> WITH
  2     temp
  3     AS
  4        (    SELECT DATA_SOURCE,
  5                    REGEXP_SUBSTR (DATA_SOURCE,
  6                                   'A-\S |ABC\S ',
  7                                   1,
  8                                   LEVEL) AS REF_NUM
  9               FROM (SELECT '
 10  Z-TEST
 11  Y-TEST
 12  A-123456789
 13  ABC123456790
 14  Y-TRY
 15  A-123456791
 16  ABC123456792
 17  ABC123456793
 18  Y-TRY
 19  Y-TRY
 20  Z-TEST
 21  Y-TEST
 22  Z-TEST
 23  Y-TEST
 24  A-123456794
 25  ABC123456795
 26  ABC123456796
 27  Y-TRY
 28  Z-TEST
 29  Y-TEST
 30  Z-TEST
 31  Y-TEST
 32  A-123456797
 33  ABC123456798
 34  ' DATA_SOURCE FROM DUAL)
 35         CONNECT BY LEVEL <= REGEXP_COUNT (data_source, CHR (10)))

 36  SELECT ref_num
 37    FROM temp
 38   WHERE ref_num IS NOT NULL;

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

10 rows selected.

SQL>

CodePudding user response：

Your connect by clause should be

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

instead of

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

So, your query should be this :

SELECT     --DATA_SOURCE, 
  REGEXP_SUBSTR(DATA_SOURCE,'A-\S |ABC\S ',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 |ABC\S ', 1)
;

demo