split the regular expression and loop through

Question

I need to loop through inside a join thats what I think I have written. I am posting the code.

    select listagg(request_num,',') within group (order by request_num) as request_num,segmentation_name from (
select MST.REQUEST_NUM,seg_dtls.SEGMENT_NAME,LAST_UPDATED_date,seg_dtls.segmentation_name from 
(select  * from rp_sr_master ) Mst,
(select  SUBSTR(ANSWER,1,INSTR (ANSWER, '~', 1)-1) AS SM_ID,sr_id from rp_sR_details 
WHERE Q_ID in (SELECT Q_ID FROM RP_QUESTIONS WHERE field_id='LM_LRE_Q6')
    ) Dtls, (select SM_ID, SQL_STATEMENT, CREATION_DATE, UPDATED_DATE, SEGMENT_NAME,segmentation_name ,TOTAL_COUNT
from rp_sEGMENT_master ) seg_dtls
where Dtls.SM_ID=seg_dtls.SM_ID
and Dtls.sr_id=Mst.sr_id)
group by segmentation_name;

The problem I am facing here is in the following,

(select  SUBSTR(ANSWER,1,INSTR (ANSWER, '~', 1)-1) AS SM_ID,sr_id from rp_sR_details 
    WHERE Q_ID in (SELECT Q_ID FROM RP_QUESTIONS WHERE field_id='LM_LRE_Q6')
        )

In the above code, answer will be something like this:

2603~NG non IaaS IT Professional^2600~NG non IaaS Senior IT^2598~NG data profiling SENIOR IT professional^2595~Nigeria data profiling IT professiona

It only picks the first number that is 2603 and others will be left out.

Is there any way I can loop through all the number in that 'ANSWER'. I am looking for ideas.

Thanks.

Answer 1

One idea is to use a method for splitting a comma delimited string into rows,
you can find examples of this method in the following answers:

Splitting comma separated values in Oracle

How can I use regex to split a string, using a string as a delimiter?

The above solutions use regexp_substr function.
If you dig into details of Oracle's REGEXP_SUBSTR function you wil find that there is optional position parameter there.

This parameter can be combined with a sulution shown in this answer:
SQL to generate a list of numbers from 1 to 100
(that is SELECT LEVEL n FROM DUAL CONNECT BY LEVEL <= 100) in the below way:

with xx as (
select '2603~NG non IaaS  IT Professional^2600~NG non IaaS Senior '
       || 'IT^2598~NG data profiling SENIOR IT professional^2595~Nigeria '
       || 'data profiling IT professiona' as answer
from dual
)
select LEVEL AS n, regexp_substr( answer, '\d+',  1, level) as nbr
from xx
connect by level <= 6
;

---

The above query produces the following result:

N |NBR  |
--|-----|
1 |2603 |
2 |2600 |
3 |2598 |
4 |2595 |
5 |     |
6 |     |

What we need is to eliminate null values from the resultset, it can be done using a simple condition IS NOT NULL

with xx as (
select '2603~NG non IaaS  IT Professional^2600~NG non IaaS Senior '
       || 'IT^2598~NG data profiling SENIOR IT professional^2595~Nigeria '
       || 'data profiling IT professiona' as answer
from dual
)
select LEVEL AS n, regexp_substr( answer, '\d+',  1, level) as nbr
from xx
connect by regexp_substr( answer, '\d+',  1, level) IS NOT NULL
;

N |NBR  |
--|-----|
1 |2603 |
2 |2600 |
3 |2598 |
4 |2595 |

---

The above query works perfect for a single record, but gets confused when we try to parse 2 or more rows. Luckily there is another answer on SO that helps to solve this issue:

Is there any alternative for OUTER APPLY in Oracle?

---

--  source data
WITH xx as (
select 1 AS id,
       '2603~NG non IaaS  IT Professional^2600~NG non IaaS Senior '
       || 'IT^2598~NG data profiling SENIOR IT professional^2595~Nigeria '
       || 'data profiling IT professiona' as answer
from dual
UNION ALL
select 2 AS id,
       '11111~NG non IaaS  IT Professional^22222~NG non IaaS Senior '
       || 'IT^2598~NG data 33333 profiling SENIOR IT professional^44~Nigeria '
       || 'data profiling 5 IT professiona 66' as answer
from dual
)
-- end of source data


SELECT t.ID, t1.n, t1.nbr
FROM xx t
CROSS JOIN LATERAL (
        select LEVEL AS n, regexp_substr( t.answer, '\d+',  1, level) as nbr
        from dual
        connect by regexp_substr( t.answer, '\d+',  1, level) IS NOT NULL
) t1;

the above query parses numbers from two records and displays then in the following form:

ID |N |NBR   |
---|--|------|
1  |1 |2603  |
1  |2 |2600  |
1  |3 |2598  |
1  |4 |2595  |
2  |1 |11111 |
2  |2 |22222 |
2  |3 |2598  |
2  |4 |33333 |
2  |5 |44    |
2  |6 |5     |
2  |7 |66    |

---

I belive you will manage to merge this simple "parsing" query into your main query.