Mariadb using IF in trigger to fill null value on insert

Question

Trying to use a trigger to a) fill in a new column top_category with values left of a colon, found in category, then b) If there is no colon, then put entire insert value into top_category. I can't get this second trigger structured to create.

https://dbfiddle.uk/jwLle13e

CREATE TABLE qn_txs (
 id SERIAL PRIMARY KEY,
 category VARCHAR(250),
 top_category VARCHAR(250)
);
-- Stage 1 trigger works
-- CREATE TRIGGER `t_add_top_category1` BEFORE INSERT ON `qn_txs` 
-- FOR EACH ROW 
-- SET NEW.`top_category` = LEFT(NEW.category, INSTR(NEW.category, ":") - 1);
DELIMITER //
CREATE TRIGGER `t_add_top_category2` BEFORE INSERT ON `qn_txs` 
 FOR EACH ROW BEGIN
 SET NEW.`top_category` = LEFT(NEW.category, INSTR(NEW.category, ":") - 1)
 -- make sure top_category is not null if there is a value in category
 IF (NEW.`top_category` = '')
 THEN
 SET NEW.`top_category` = NEW.`category`;
 END IF;
END//
DELIMITER;
INSERT INTO qn_txs (category)
VALUES ('I should also be a top cat'), ('bananas:green'), ('baseball:table'), (''), ('Very long string: with spaces');
SELECT * FROM qn_txs;

Here's a table of the desired output after the insert is complete.

id	category	top_category
1	I should also be a top cat	I should also be a top cat
2	bananas:green	bananas
3	baseball:table	baseball
4
5	Very long string: with spaces	Very long string

• Rick James: For the first VALUE, you want that phrase in category also? Show us thedelivered result and the desired result after the INSERT.
• br8k: Here's another attempt at the trigger, but it still fails to create and errors are not helpful : ``` DELIMITER // CREATE TRIGGER t_add_top_category BEFORE INSERT ON qn_txs FOR EACH ROW BEGIN IF(INSTR(NEW.category, ":") = 0) THEN SET NEW.top_category = NEW.category; ELSE SET NEW.top_category = LEFT(NEW.category, INSTR(NEW.category, ":") - 1); END IF; END // DELIMITER ; ```

Answer 1

CREATE TRIGGER `t_add_top_category2` 
BEFORE INSERT ON `qn_txs` 
FOR EACH ROW
SET NEW.top_category = SUBSTRING_INDEX(NEW.category, ':', 1);

https://dbfiddle.uk/_ru7qse3

• br8k: Brilliant! Thank you!