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:
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.
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>