Instead substr using other functions

Help Procedural SQL
1

Hi Team

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

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

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 

loop 

  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

2

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