SQL Query – Find invalid email addresses

As a database administrator one of the important health checks you can perform is checking that email addresses being stored are valid.

It is near impossible to programmatically define what makes a valid email address, but you can get 99% close using a range of conditional queries or REGEXP terms.

The SQL select query below will check for emails that are not in the typical format of address@domain.tld as well as characters that are not allowed – such as spaces, commas, forward and back-slashes.

If you find invalid email addresses in your database you will need to first determine how they ended up in the database – were they imported into the database or does your system allow invalid email addresses to be entered – and fix that issue. Once your system does not allow invalid emails to be saved to the database you can then fix or remove the invalid email addresses.

Note: the query will need to be modified to suit your database, such as the table and column names.

 

SELECT     Contact_Detail_ID, Number, Email_Address, URL
FROM         T_Contact_Details
WHERE     (Email_Address NOT LIKE '%_@_%_.__%') OR
(Email_Address LIKE '% %') OR
(Email_Address LIKE '%<%') OR
(Email_Address LIKE '%>%') OR
(Email_Address LIKE '%(%') OR
(Email_Address LIKE '%)%') OR
(Email_Address LIKE '%[%') OR
(Email_Address LIKE '%]%') OR
(Email_Address LIKE '%;%') OR
(Email_Address LIKE '%:%') OR
(Email_Address LIKE '%,%') OR
(Email_Address LIKE '%%') OR
(Email_Address LIKE '%/%')

SQL Query - Find invalid email addresses