How can I extract members of a csv column in a MySQL table that contain a particular character?

I have a column 开发者_运维知识库in a MySQL table that is a mediumtext that is formatted as csv. I'm interested in extracting the members of these csv lists that contain the character '*'. What's the simplest way to do this?

---

I've never done something similar. As you can see I made a simple test. I have no idea about performance on large recordset.

create table mytest (
id int not null auto_increment primary key,
content varchar(100)
) engine = myisam;

insert into mytest (content)
values 
('one,two*,three*text'),
('four,five'),
('six*,seven*,eight*');



delimiter //
drop procedure if exists split_found //
create procedure split_found()
begin
declare str varchar(200);
declare ssql varchar(200);
declare finito int default 0;
declare cursore cursor for select content from mytest;
declare continue handler for not found set finito = 1;
drop temporary table if exists tmp;
create temporary table tmp (cont varchar(50) );
open cursore;
mio_loop:loop
fetch cursore into str;
if finito = 1 then
leave mio_loop;
end if;
 set @ssql = concat("insert into tmp (cont) values ('", replace(str, ',', "'),('"), "')");
 prepare stmt from @ssql;
 execute stmt;
 deallocate prepare stmt;
end loop;
close cursore;
select * from tmp where cont like '%*%';
end; //
delimiter ;

mysql> call split_found();
+------------+
| cont       |
+------------+
| two*       |
| three*text |
| six*       |
| seven*     |
| eight*     |
+------------+
5 rows in set (0.01 sec)