title: split the regular expression and loop through
body: <p>I need to loop through inside a join thats what I think I have written.
  I am posting the code.</p>
  
  <pre><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;
  </code></pre>
  
  <p>The problem I am facing here is in the following,</p>
  
  <pre><code>(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')
          )
  </code></pre>
  
  <p>In the above code, answer will be something like this:<br><br>
  <code>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</code><br><br>
  It only picks the first number that is 2603 and others will be left out.</p>
  
  <p>Is there any way I can loop through all the number in that 'ANSWER'.
  I am looking for ideas. </p>
  
  <p>Thanks.</p>