sql regex check constraint

Question

in mysql i trying to verify a valid email but i don't know how to use a regex with constraint i don't want use TRIGGER it is possible??!

MySQL does not support `CHECK` constraint so IMHO it seems not possible to do it without trigger. — Ozan, Dec 15 '15 at 21:22
You are trying to verify when selecting or inserting/updating? You could verify server side for the inserting/updating. You could select with a regex in where clause. — chris85, Dec 15 '15 at 21:27
From version 8, MySQL does support `check`constraints: https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html — Renaat De Muynck, Jun 09 '21 at 19:15

score 1 · Answer 1 · edited May 23 '17 at 11:50

1

In mySql you could use this for valid email or NOT REGEX for find the invalid

select email from your_table where email  
        REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$';

or for ORACLE

SELECT email 
FROM your_table
WHERE REGEXP_LIKE (email, '[A-Z0-9._%-]+@[A-Z0-9._%-]+\.[A-Z]{2,4}');

but remember is not an absolute solution for this you could find useful this SO post Using a regular expression to validate an email address

edited May 23 '17 at 11:50

Community

answered Dec 15 '15 at 22:07

ScaisEdge

no i don't went to select from database i wen't add a constraint when i went to insert vales in my table – ayoub naimi Dec 16 '15 at 17:41
For this you should create trigger, ( see how create trigger) and apply the regexp in answer http://stackoverflow.com/questions/16005283/is-it-possible-to-enforce-data-checking-in-mysql-using-regular-expression – ScaisEdge Dec 16 '15 at 17:44
thanks for your help,i'm beginner and i look for a simple method – ayoub naimi Dec 16 '15 at 18:11
If you want to add constraints to the database solution it is the trigger I do not think there are easier solutions. If you want to use simple solutions to write because you start checking on the values before inserting them in the db – ScaisEdge Dec 16 '15 at 18:41

1 Answers1