Conflicting 'LIKE' conditions in MySQL CASE statement

Question

I have two conditions that are causing the CASE statement not to work as intended. I've tried changing which condition comes first, but it still only proceeds with one of them. Any suggestions?

CASE statement that returns only the value before the first '(' and replaces all instances of 'Metallic' with 'M':

CASE
WHEN `order_option`.`value` LIKE '%(%)%' THEN SUBSTRING_INDEX(`order_option`.`value`,' (', 1)
WHEN `order_option`.`value` LIKE '%Metallic%' THEN REPLACE (`order_option`.`value`,'Metallic','M')
ELSE `order_option`.`value`
END AS `Option`,

Current data

`value`
--------
Red Metallic (999)
Blue (745)

Expected results

`Option`
--------
Red M
Blue

Thank you in advance!

Answer 1

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