Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Is there a way to convert a function that uses recursive regexp into "plain" SQL?

JonWatJun 10 2021

I have a function that selects out the first words in a given string if they are all caps (plus a couple of punctuation marks). I now need to do the same thing in an environment where I cannot use functions, and where CTE functions cannot be used (a Direct Database Request in OBIEE 11.1.1.7.).
Below is the contents of the function which works fine given a single row of input, but which cannot handle a set of data (i.e. the two rows in the full testdata). Is there any way to get the same result using just SQL?
Thanks.

with testdata as
 --(select 'END ORGNAME Begin address' p_address from dual union all
 (select 'NEWFOUNDLAND SOCIETY INC 24 High Street' p_address from dual)
select listagg(apart,' ') within group (order by lv) trans_string,length(listagg(apart,' ') within group (order by lv)) trans_string_length
 from 
  (SELECT regexp_substr(p_address,'[^ ]+',1,level) apart,level lv
   from testdata
   connect by level <= REGEXP_COUNT (p_address,'[^ ]+')
  )t  
 where t.lv <= 
      ( select nvl(min(case when isvalid = 0 then lv end) - 1, count(*)) 
        from
          (select lv,ispo,case when not (ispo = 1 or rv= 0) then 1 else 0 end isvalid
           from
             (select lv,apart,regexp_instr(apart,'^[A-Z&,.]*$') rv,case when apart in ('PO','P.O.') then 1 else 0 end ispo
              from 
                (SELECT regexp_substr(p_address,'[^ ]+',1,level) apart,level lv
                 from testdata
                 connect by level <= REGEXP_COUNT (p_address,'[^ ]+')
             )
           )   
        ));

Thanks in advance.

This post has been answered by Solomon Yakobson on Jun 11 2021
Jump to Answer

Comments

Post Details

Added on Jun 10 2021
4 comments
330 views