How to validate email addresses in MySQL

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

SELECT * FROM `contacts` WHERE `email` REGEXP '^[A-Z0-9._%-][email protected][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._%-][email protected][A-Z0-9.-]+\.[A-Z]{2,10}[[:space:]]*$'
If you put it in Java, you need to escape like:

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

See also

MySQL regular expression Example

Post a Comment

Featured Post

Updated Coupons / promo codes for Google Apps for Work / Business

To redeem: 1. Sign up for Google Apps   2. Go to your billing settings  3. Choose your payment plan  4. Enter your promo code ...