Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

split and store values from multiple variables

shahid_patelJan 11 2023

Hi,
I have below code working fine for single variable values split by | sign. Now I have 3 or more variables all split with | sign. I need to get all them and store in table , after each | (pipe sign) value..

declare

pval varchar2(200) := 'shahid|ahmed|patel';
pid varchar2(200) := 'first name|middle name|last name';
pot varchar2(200) := 'start|middle|end';

v_sep varchar2(1) := '|';
begin

for cc in (
select regexp_substr(pval, '[^' || v_sep || ']+' , 1, comma.column_value) as variable
from table(cast(multiset(select level from dual connect by level <= length (regexp_replace(pval, '[^' || v_sep || ']+')) + 1) as sys.OdciNumberList)) comma
)
loop
dbms_output.put_line('value is ' || cc.variable);

    /\*  
    how i get other two variable values here it shud print like   
    >> first name is shahid (start)  
    >> middle name is ahmed (middle)  
    >> last name is patel (end)  
     \*/          
       
    --store value in variaable and do other insert/update  
 end loop;  

end;

if not possible with above code please guide for alternate solution

Thanks,

This post has been answered by mathguy on Jan 11 2023
Jump to Answer
Comments
Post Details
Added on Jan 11 2023
5 comments
302 views