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
– Ozan, Commented Dec 15, 2015 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
– chris85, Commented Dec 15, 2015 at 21:27
From version 8, MySQL does support checkconstraints: dev.mysql.com/doc/refman/8.0/en/… — Renaat De Muynck
– Renaat De Muynck, Commented Jun 9, 2021 at 19:15

Community · Accepted Answer · 2017-05-23 11:50:32Z

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, 2017 at 11:50

CommunityBot

11 silver badge

answered Dec 15, 2015 at 22:07

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

4 Comments

ayoub naimi Over a year ago

no i don't went to select from database i wen't add a constraint when i went to insert vales in my table

ScaisEdge Over a year ago

For this you should create trigger, ( see how create trigger) and apply the regexp in answer stackoverflow.com/questions/16005283/…

ayoub naimi Over a year ago

thanks for your help,i'm beginner and i look for a simple method

ScaisEdge Over a year ago

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

Collectives™ on Stack Overflow

sql regex check constraint

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related