I have a string that is something like this:
(InstrTyp EQ DebtInstruments) AND (IntrnlTrdTyp EQ IntraGrpBP) AND (Entity EQ GSSTH)
I would like to save these in an array such that :
InstrTyp EQ DebtInstruments IntrnlTrdTyp EQ IntraGrpBPEntity EQ GSSTHNew to PL/SQL, appreciate a verbose answer.
You may use regexp_substr to extract the string between parentheses
DECLARE
TYPE string_array_typ IS
TABLE OF VARCHAR2(100);
split_strs string_array_typ := string_array_typ(); --define and declare an array of string
l_str_to_split VARCHAR2(1000) := '(InstrTyp EQ DebtInstruments) AND (IntrnlTrdTyp EQ IntraGrpBP) AND (Entity EQ GSSTH)'
;
BEGIN
FOR i IN 1..regexp_count(l_str_to_split,'\(.*?\)') --loop until as many number of strings between `()`
LOOP
split_strs.extend;
split_strs(i) := regexp_substr(l_str_to_split,'\((.*?)\)',1,i,NULL,1); -- Assign ith element to ith word between `()`
END loop;
FOR i IN 1..split_strs.count LOOP
dbms_output.put_line(split_strs(i) ); --display the contents of the array
END LOOP;
END;
/
We can try doing this in two steps. First, remove all parentheses from the input string, then do a regex split to array on the pattern \s*AND\s*:
select
regexp_split_to_array(regexp_replace(txt, '[()]', '', 'g'), '\s*AND\s*')
from your_table;
