arraysoracleplsqlsplit

how to split a string and store in array in pl/sql


i need to split a string and store in array, i have tried the following pl/sql code,

SELECT y.* 
FROM (
    select trim(regexp_substr(str,'[^$]+', 1, level)) as str1
    from ( 
       SELECT 'key1:string$key2:bring$key3:T-ring$' as Str 
       FROM dual 
    )
    connect by regexp_substr(str, '[^$]+', 1, level) is not null
) x
CROSS APPLY(
    select trim(regexp_substr(str1,'[^:]+', 1, 1)) as key,
           trim(regexp_substr(str1,'[^:]+', 1, 2)) as value
    from dual
) y

colon(:) is used for key value sepration and dollor($) is used for separation.

output:

enter image description here

it worked for me, it is printing the output, but i need to store the output in a variable/array, and i am not getting how to store the returned value into an array(key-value pair) for validation purpose.


Solution

  • Here's one option.

    Create types to hold data:

    SQL> create or replace type t_row as object (key varchar2(10), value varchar2(10));
      2  /
    
    Type created.
    
    SQL> create or replace type t_tab is table of t_row;
      2  /
    
    Type created.
    

    Procedure which a) stores data into a collection, b) displays the result:

    SQL> set serveroutput on
    SQL> declare
      2    l_tab t_tab;
      3    l_str varchar2(100) := 'key1:string$key2:bring$key3:T-ring';
      4  begin
      5    with temp as
      6      (select regexp_substr(l_str, '[^$]+', 1, level) val
      7       from dual
      8       connect by level <= regexp_count(l_str, '\$') + 1
      9      )
     10    select t_row(substr(val, 1, instr(val, ':') - 1),
     11                 substr(val, instr(val, ':') + 1)
     12                )
     13    bulk collect into l_tab
     14    from temp;
     15
     16    for i in l_tab.first .. l_tab.last loop
     17      dbms_output.put_line(l_tab(i).key ||' - '|| l_tab(i).value);
     18    end loop;
     19  end;
     20  /
    key1 - string
    key2 - bring
    key3 - T-ring
    
    PL/SQL procedure successfully completed.
    
    SQL>
    

    If you want to use locally-defined types (the ones that exist only in your PL/SQL procedure), then

    SQL> declare
      2    type  t_row is record (key varchar2(10), value varchar2(10));
      3    type  t_tab is table of t_row;
      4    l_tab t_tab;
      5    l_str varchar2(100) := 'key1:string$key2:bring$key3:T-ring';
      6  begin
      7    with temp as
      8      (select regexp_substr(l_str, '[^$]+', 1, level) val
      9       from dual
     10       connect by level <= regexp_count(l_str, '\$') + 1
     11      )
     12    select substr(val, 1, instr(val, ':') - 1),
     13                 substr(val, instr(val, ':') + 1)
     14    bulk collect into l_tab
     15    from temp;
     16
     17    for i in l_tab.first .. l_tab.last loop
     18      dbms_output.put_line(l_tab(i).key ||' - '|| l_tab(i).value);
     19    end loop;
     20  end;
     21  /
    key1 - string
    key2 - bring
    key3 - T-ring
    
    PL/SQL procedure successfully completed.
    
    SQL>