0

I have to split the declared string delimited by ; into rows as below. It works well if I declare the string and split it. Instead of declaring, I have to select this string from a table and parse it repeatedly. Each row can contain different number of strings.

What would be the best approach?? Should I loop through each row, parse them or is there a better way of doing it??

--Required output

status := off
status:= on

--QUERY

declare
myString varchar2(2000):='status := off; status:= on;';
begin
     for r in
      ( select regexp_substr(myString,'[^;]+',1,level) element
          from dual
       connect by level <= length(regexp_replace(myString,'[^;]+')) + 1
      )
      loop
       dbms_output.put_line(r.element);
     end loop;
   end;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
texasnewbie
  • 1
  • 2
  • It looks like this might be a question about Oracle? You may wish to [edit] your question and add `sql` and `oracle` to your question to get the question in front of the right audience. – Heretic Monkey Dec 31 '19 at 19:50
  • @BobJarvis-ReinstateMonica It is a dupe, there are plenty of questions on splitting delimited strings in Oracle; if you wish to find a better duplicate then please add/edit the list of duplicates. I've already added a second duplicate on splitting from a table and am looking for more. You have the reputation to do the same. Also, the OP does not rule out a variable in PL/SQL they are asking for opinions on a "best" way to do it.... so even if it is not a duplicate (it is) then it should be closed as primarily opinion-based. – MT0 Dec 31 '19 at 20:43

1 Answers1

0

There's really not much difference - you select from your table instead of DUAL, using the appropriate column instead myString:

SELECT TRIM(REGEXP_SUBSTR(A_STRING,'[^;]+',1,LEVEL)) ELEMENT
  FROM SOME_TABLE
  CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(A_STRING,'[^;]+')) + 1

I tossed a TRIM in there because the second row had an extra blank in front of it. Leave in or remove at our option.

db<>fiddle here

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
  • Thank you. When I try to do a select as specified, it never pulls any rows. I changed the connect by level > 0 and it threw a "not enough memory for connect by operation" message. – texasnewbie Dec 31 '19 at 21:13
  • `LEVEL` is always > 0, so `CONNECT BY LEVEL > 0` is basically an infinite loop. OK, simplify. What happens if you do `SELECT whatever FROM YOUR_TABLE`? If that produces data, then start adding elements until things break. Then figure out why it broke. – Bob Jarvis - Слава Україні Dec 31 '19 at 22:46