As a database administrator one of the important data 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 [email protected] 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 '%/%')
