Instead substr using other functions

Hi Team

I have scenario that i need to split the data into with placeholder @ and ,


emp_id =A1234

dept_id = d456

and insert into emp table with column emp_id,dept_id

i am trying with using log is like

v_name = ‘A1234@b456,sup676@RWx765,jkh345@uiy875’   

WHILE v_name is not null 


  l_supp_id = trim(substr (str1, 1, instr(str1, delimit2)-1) );

  l_sch_id = trim(substr (str1, instr(str1, delimit2)+ 1) ) ;

insert into table

end loop

insert of loop is there any other logic to work this kind of scenario

Thanks in advance

Using procedural code here seems reasonable. An alternative would be to make it into a JSON array of strings, e.g. replace every comma to be


then put


at the beginning and


at the end, and then do

select * from table(json_to_array(json_array_string_var));

1 Like