Digital Marketing

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( REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\\\\.[A-Z]{2,18}$'

See also

MySQL regular expression Example


Popular posts from this blog

Fixed WSL sshd: no hostkeys available -- exiting

PowerMTA pmta command

How to fix: mv: failed to preserve ownership