regexp_substr

 SELECT level,regexp_substr('Apple,Orange,Mango,Grapes','[^,]+',1,level) regexp_substr

FROM dual

  CONNECT BY level<=regexp_count('Apple,Orange,Mango,Grapes',',')+1;

SELECT ltrim(substr(text,1,15),'Order No'),length(('Order No 11069 RE Instalment ID 27')),

case 

     when length(text)=30 then 

     ltrim(substr(text,1,14),'Order No')

     when length(text) = 31 then

     ltrim(substr(text,1,15),'Order No')

     when length(text) =32 then

     ltrim(substr(text,1,16),'Order No')

     when length(text) = 33 then

     ltrim(substr(text,1,17),'Order No')

     when length(text) = 34 then

     ltrim(substr(text,1,18),'Order No')

     END ORDER_NO

from GL_AND_HOLD_VOU_ROW_QRY

where ACCOUNT='1221100'

and voucher_type='M'

select sum(CREDIT_AMOUNT) realized_Int, 0 unearned_Int,

       (select i.CREATORS_REFERENCE 

            from IAL_C_ORDER_BASE_INVOICE_VIEW i 

            left join IFSAPP.IAL_C_ORDER_BASE_INVOICE_VIEW b

            ON i.SERIES_ID||''||i.INVOICE_NO=b.SERIES_ID||''||b.INVOICE_NO

            WHERE (gl.REFERENCE_SERIE||''||gl.REFERENCE_NUMBER=i.SERIES_ID||''||i.INVOICE_NO))

from GL_AND_HOLD_VOU_ROW_QRY gl

where ACCOUNT='1221100'

and voucher_type='F'

UNION ALL 

select 0 realized_Int, sum(DEBET_AMOUNT)  unearned_Int,

   regexp_replace(substr(TEXT,10), '( [^ ]+){3}$', '')

/*  CASE 

     WHEN LENGTH(TEXT)=30 THEN 

        LTRIM(SUBSTR(TEXT,1,14),'Order No')

     WHEN LENGTH(TEXT) = 31 THEN

        LTRIM(SUBSTR(TEXT,1,15),'Order No')

     WHEN LENGTH(TEXT) =32 THEN

        LTRIM(SUBSTR(TEXT,1,16),'Order No')

     WHEN LENGTH(TEXT) = 33 THEN

        LTRIM(SUBSTR(TEXT,1,17),'Order No')

     WHEN LENGTH(TEXT) = 34 THEN

        LTRIM(SUBSTR(TEXT,1,18),'Order No')

     END ORDER_NO

  */

from GL_AND_HOLD_VOU_ROW_QRY gl

where ACCOUNT='1221100'

and voucher_type='M'

and text != 'Realization of Unrealized Interest' 

select regexp_replace(substr(TEXT,10), '( [^ ]+){3}$', '') xaa --regexp_substr(text, '(.*?)( |$)', 1, 3, NULL, 1) col3,text,length(text)

from GL_AND_HOLD_VOU_ROW_QRY gl

where ACCOUNT='1221100'

and voucher_type='M'

and text != 'Realization of Unrealized Interest' 

and length(text)>32

Order No 16072 R Instalment ID 51 16072 R

Order No 16072 R Instalment ID 52 16072 R

Order No 40735 Instalment ID 8 40735

Order No 40735 Instalment ID 9 40735