Is it Possible to Enforce Data Checking in MySQL using Regular expression

by

Yes, you can. MySQL supports regex (http://dev.mysql.com/doc/refman/5.6/en/regexp.html) and for data validation you should use a trigger since MySQL doesn’t support CHECK constraint (you can always move to PostgreSQL as an alternative:). NB! Be aware that even though MySQL does have CHECK constraint construct, unfortunately MySQL (so far 5.6) does not validate data against check 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 check constraint for a column phone:

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 ('+64-221221442'); -- should be OK
INSERT INTO data VALUES ('+64-22122 WRONG 1442'); -- will fail with the error: #1644 - Wroooong!!!

However you should not rely merely on MySQL (data layer in your case) for data validation. The data should be validated on all levels of your app.

More Related Contents:

  1. How to do a regular expression replace in MySQL?
  2. How to abort INSERT operation in MySql trigger?
  3. MySql Error: Can’t update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger
  4. MySQL Trigger after update only if row has changed
  5. MySQL Split Comma Separated String Into Temp Table
  6. MySQL strip non-numeric characters to compare
  7. How to extract two consecutive digits from a text field in MySQL?
  8. How to extract the nth word and count word occurrences in a MySQL string?
  9. How to remove all non-alpha numeric characters from a string in MySQL?
  10. How to count words in MySQL / regular expression replacer?
  11. mysql after insert trigger which updates another table’s column
  12. #1139 – Got error ‘repetition-operator operand invalid’ from regexp
  13. How to program a MySQL trigger to insert row into another table?
  14. TRIGGERs that cause INSERTs to fail? Possible?
  15. Use a trigger to stop an insert or update
  16. MySQL Fire Trigger for both Insert and Update
  17. MySQL trigger On Insert/Update events
  18. Can a MySQL trigger simulate a CHECK constraint? [duplicate]
  19. MySQL error 1436: Thread stack overrun, with simple query
  20. mysql trigger stored trigger is already used by statement which invoked stored trigger
  21. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  22. Does MySQL Regexp support Unicode matching
  23. How do I refer to capture groups in a MySQL regex?
  24. Can I launch a trigger on select statement in mysql?
  25. How to regex in a MySQL query
  26. Using MySQL triggers to log all table changes to a secondary table
  27. MySQL: How do I use delimiters in triggers?
  28. MySQL listen notify equivalent
  29. Calling an url from a trigger in mysql
  30. mySQL regex in the where clause

Leave a Comment