CREATE TABLE table1 (`color` VARCHAR(6), `rank` INTEGER); INSERT INTO table1 (`color`, `rank`) VALUES ('Red', '1'), ('Blue', '2'), ('Green', '3'), ('Yellow', '4'), ('White', '5'), ('Black', '6'); CREATE TABLE table2 (`id` VARCHAR(3), `ColorValue` VARCHAR(19)); INSERT INTO table2 (`id`, `ColorValue`) VALUES ('ID1', 'Red,Yellow'), ('ID2', 'Green'), ('ID3', 'White'), ('ID4', 'Green,Red'), ('ID5', 'Yellow, White,Black');

SELECT DISTINCT t2.id, t2.ColorValue, FIRST_VALUE(t1.color) OVER (PARTITION BY t2.ColorValue ORDER BY t1.`rank`) Value_to_be_replaced FROM table2 t2 INNER JOIN table1 t1 ON FIND_IN_SET(t1.color, t2.ColorValue) ORDER BY t2.id

SELECT t2.*, ( SELECT t1.color FROM table1 t1 WHERE FIND_IN_SET(t1.color, t2.ColorValue) ORDER BY t1.`rank` LIMIT 1 ) Value_to_be_replaced FROM table2 t2 ORDER BY t2.id