Example script to extract one item from a list of values in MySQL - Use Prepare and Execute - Dynamic SQL

If you have a list of ids in a string and then you want to extract each element from the array of elements, then you can use the below sample script to extract each item.

We are using a while loop and the substring_index function to extract each item.
We have passed the table name as the parameter to the procedure. The rows will be concatenated and then each element from the array is extracted.

The data in my table looks as follows: I need each ID field stored in the last column. The below stored will accomplish this.

The values in column are to be merged using group_concat() to form an array

Example script:


drop procedure if exists eligibilityfile_str_to_column;
DELIMITER $$
CREATE PROCEDURE eligibilityfile_str_to_column(v_tbl_name varchar(200))
begin

DECLARE v_data_extract, v_str_len, v_counter bigint;
DECLARE v_file_type, v_staging_id_list,v_stmnt varchar(500);

set v_data_extract = 0;
set v_str_len = 0;
set v_counter = 1;

select v_tbl_name into @a;
set @v_stmnt = concat('select group_concat(eligibility_file_staging_complete_id) into @v_staging_id_list from ', v_tbl_name);

PREPARE stmt2 from @v_stmnt;
select concat('select group_concat(eligibility_file_staging_complete_id) into @v_staging_id_list from ', v_tbl_name);

EXECUTE stmt2;

# find the total no of delimiters
select char_length(@v_staging_id_list) - char_length(replace(@v_staging_id_list,',','')) into v_str_len;



select concat('length is',v_str_len);

drop temporary table if exists temp_staging_list;
create temporary table temp_staging_list
(staging_id bigint(20) unsigned
);

select 'table created';

# loop to read till all the elements are not read
while v_counter <= v_str_len do
 #select 'loop started';
 select substring_index(substring_index(@v_staging_id_list,',',v_counter),',',-1) into v_data_extract;
 #insert into temp_staging_list select v_data_extract;
select v_data_extract;
 set v_counter = v_counter + 1;
end while;

end$$
DELIMITER ;