Is it possible to perform data validation in MySQL using a regular expression

Question

Suppose I have an attribute called a phone number, and I would like to apply a certain value to the entries in this field. Can I use regex for this purpose, since regex is very flexible in defining constraints.

Answer 1

Yes, you can. MySQL supports regex ( http://dev.mysql.com/doc/refman/5.6/en/regexp.html ), and you must use a trigger to validate the data, since MySQL does not support the CHECK constraint (you can always go to PostgreSQL in as an alternative :). NB! Keep in mind that although MySQL has a CHECK constraint construct, unfortunately MySQL (so far 5.6) does not check data for control constraints. According to http://dev.mysql.com/doc/refman/5.6/en/create-table.html : "The CHECK clause is parsed, but ignored by all storage engines."

You can add a control restriction for the telephone column:

CREATE TABLE data ( phone varchar(100) ); DELIMITER $$ CREATE TRIGGER trig_phone_check BEFORE INSERT ON data FOR EACH ROW BEGIN IF (NEW.phone REGEXP '^(\\+?[0-9]{1,4}-)?[0-9]{3,10}$' ) = 0 THEN SIGNAL SQLSTATE '12345' SET MESSAGE_TEXT = 'Wroooong!!!'; END IF; END$$ DELIMITER ; INSERT INTO data VALUES ('[phone]'); -- should be OK INSERT INTO data VALUES ('+64-22122 WRONG 1442'); -- will fail with the error: #1644 - Wroooong!!! 

However, you should not rely solely on MySQL (the data layer in your case) to validate the data. Data must be verified at all levels of your application.