procedimiento almacenado

delimiter $$
DROP PROCEDURE IF EXISTS sp_split$$
CREATE PROCEDURE sp_split(str nvarchar(6500), dilimiter varchar(15), tmp_name varchar(50))
BEGIN
declare end_index   int;
declare part        nvarchar(6500);
declare remain_len  int;

set end_index      = INSTR(str, dilimiter);

while(end_index   != 0) do

    /* Split a part */
    set part       = SUBSTRING(str, 1, end_index - 1);

    /* insert record to temp table */
    call `sp_split_insert`(tmp_name, part);

    set remain_len = length(str) - end_index;
    set str = substring(str, end_index + 1, remain_len);

    set end_index  = INSTR(str, dilimiter);

end while;

if(length(str) > 0) then

    /* insert record to temp table */
    call `sp_split_insert`(tmp_name, str);

end if;
END
$$


DROP PROCEDURE IF EXISTS sp_split_insert$$
CREATE PROCEDURE sp_split_insert(tb_name varchar(255), tb_value nvarchar(6500))
BEGIN
 SET @sql = CONCAT('Insert Into ', tb_name,'(item) Values(?)');
 PREPARE s1 from @sql; SET @paramA = tb_value;
 EXECUTE s1 USING @paramA;
END
$$

DROP PROCEDURE IF EXISTS test_split$$
CREATE PROCEDURE test_split(test_text nvarchar(255))
BEGIN
    create temporary table if not exists tb_search
    (
        item nvarchar(6500)
    );

    call sp_split(test_split, ',', 'tb_search');

    select * from tb_search where length(trim(item)) > 0;

    drop table tb_search;
END
$$
#call test_split('Apple,Banana,Mengo');
#$$