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 ?

We assumed if this column type is VARCHAR(12) i want to validate it with 4 strings ,2 symbols with 6 integers — underscore
– underscore, Commented Jan 5, 2014 at 17:03
If you want to do validation on the database you'll have to use sql triggers. Here is an example: cvuorinen.net/2013/05/validating-data-with-triggers-in-mysql — Ilan Frumer
– Ilan Frumer, Commented Jan 5, 2014 at 17:06

Ilan Frumer · Accepted Answer · 2014-01-05 17:24:23Z

3

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

edited Jan 5, 2014 at 17:24

answered Jan 5, 2014 at 17:17

Ilan Frumer

32.4k8 gold badges73 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to validate a string using mysql when inserting data

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related