how to validate a string using mysql when inserting data

Question

I have a data called registration number like follows

COL/A-000001,
KAL/B-000023,
BAL/A-000452,

I know how to validate this type of format using php.But i want to do it when i create the table.IS it possible ?

Answer 1

Try this:

change (example_tbl / field_name) to your (table / field) names respectively.

DELIMITER $$

CREATE TRIGGER example_before_insert
     BEFORE INSERT ON example_tbl FOR EACH ROW
     BEGIN
          IF NEW.field_name NOT_REGEXP '^[A-Z]{3}\/[A-Z]-\d{6}$' THEN
               SIGNAL SQLSTATE '45000'
                    SET MESSAGE_TEXT = 'Cannot add or update row: regex failed';
          END IF;
     END;
$$