How to validate email addresses in MySQL

SELECT * FROM `contacts` WHERE `email` REGEXP '^[^@]+@[^@]+\.[^@]{2,17}$';

SELECT * FROM `contacts` WHERE `email` REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,17}$';

Since the longest top level domain is 17. ( What is the longest top level domain (TLD))

If you would like to allow trailing spaces:

SELECT * FROM `contacts` WHERE `email`  REGEXP '^[[:space:]]*[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,10}[[:space:]]*$'

If you put it in Java, you need to escape like:

trim(c.email) REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\\\\.[A-Z]{2,18}$'

See also

MySQL regular expression Example