MySQL Forums
Forum List  »  Stored Procedures

Another way to convert log string to multiple rows with specific delimiter
Posted by: sthen lin
Date: October 29, 2018 09:45PM

Here is another way to splitting the string into multiple rows by specific delimiter.

The main idea is convert the long string to a insert SQL

Here is the SQL script:

delimiter \\
drop procedure if exists usp_spling;

create procedure usp_spling (
in data_string text,
in str_del varchar(16),
out recode int
)
this_proc:BEGIN
if length(data_string)<0 or length(str_del) <0 then
set recode=-1;
leave this_proc;
end if;
drop table if exists data_spling_;
create table data_spling_(col nvarchar(256));
set @tsql= concat('insert into data_spling_(col) values ( ''', replace(data_string,str_del ,'''),(''') , '\')');

#select @tsql;
prepare stmt from @tsql;
execute stmt;
select * from data_spling_;
end\\


How to call that stored procedure.

call usp_spling('1,2,3,4,5,6,7,8',',',@b);

select @b;

There are 3 parameters in this SP
1 The Log string that will spited into several rows
2 The delimiter.
3 out put returncode.

Options: ReplyQuote


Subject
Views
Written By
Posted
Another way to convert log string to multiple rows with specific delimiter
196
October 29, 2018 09:45PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.