Conflicting 'LIKE' conditions in MySQL CASE statement
You have no chance,  besides change the metallooc text first  and also do a substing on it

Schema (MySQL v8.0)

CREATE TABLE Option1 (
  `Text1` VARCHAR(18),
  `num` INTEGER
);

INSERT INTO Option1
  (`Text1`, `num`)
VALUES
  ('Red Metallic (999)', '0'),
  ('Blue (745)', '0');




Query #1

SELECT
CASE
WHEN `text1` LIKE '%Metallic%' THEN SUBSTRING_INDEX(REPLACE (`text1`,'Metallic','M'),' (', 1)
WHEN `text1` LIKE '%(%)%' THEN SUBSTRING_INDEX(`text1`,' (', 1)

ELSE `text1`
END AS `Option1`
FROM Option1;




| Option1 |
| ------- |
| Red M   |
| Blue    |


View on DB Fiddle