INSERT INTO tableA VALUES

(1 , 'Dugong dugon, Delphinus delphis'),

(2 , 'Balaneoptera physalus, Tursiops truncatus, Stenella coeruleoalba'),

(3 , 'Balaneoptera physalus, Tursiops truncatus, Stenella coerulealba'), -- misprint

(4 , 'Balaneoptera physalus, Tursips truncatus, Stenella coerulealba') -- 2 misprints

;

SELECT * FROM tableA;

INSERT INTO tableB VALUES

(1 , 'Dugong dugon'),

(2 , 'Delphinus delphis'),

(3 , 'Balaneoptera physalus'),

(4 , 'Tursiops truncatus'),

(5 , 'Stenella coeruleoalba');

SELECT * FROM tableB;

DECLARE sp_name VARCHAR(255);

DECLARE done INT DEFAULT FALSE;

DECLARE cur CURSOR FOR SELECT species_name FROM tableB;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE;

OPEN cur;

CREATE TEMPORARY TABLE t_tableA SELECT * FROM tableA;

FETCH cur INTO sp_name;

UPDATE t_tableA SET qualifying_species = REPLACE(qualifying_species, sp_name, '');

FETCH cur INTO sp_name;

UNTIL done END REPEAT;

CLOSE cur;

SELECT id, qualifying_species wrong_species FROM t_tableA WHERE REPLACE(qualifying_species, ',', '') != '';

DROP TABLE t_tableA;