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

How to fix Google Apps Script: Script function not found: doGet

Creating sort index in MySQL

How to fix: df shows no file systems processed on Linux