CODEWITHSUNDEEP
phpmysqlvalidation
underscore
underscore

Reputation: 6887

how to validate a string using mysql when inserting data

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 ?

Upvotes: 3

Views: 2842

Answers (1)

Ilan Frumer
Ilan Frumer

Reputation: 32357

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;
$$

Upvotes: 3

Related Questions