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.