If you have ever had the job of cleansing email addresses you will know they come in many different forms of incorrect states.
A couple of years ago I was given the task of cleansing and validating millions of email addresses, I used the query that I have supplied in this article to help clean up the mess of data that was supplied.
This query does a pretty good job of ensuring the addresses are in a good state in terms of formatting.
That doesn’t mean you can actually send an email to it, it means the basic structure is correct.
Anyway here is the T-SQL code to filter out all the garbage email addresses. I hope it helps you, as much as it helped me when I worked on that project.
T SQL Validate Email Address Example
--------------------------- USE DBAFire_DB Select * from usertable where email like '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z][a-z]%' ---------------------------
How to Use the TSQL Email Verification Query
The best way to use the query is either to identify the records that are in a good format, and separate them out (or mark them as valid).
Or alternatively you can use it to discard of the addresses that are not in a valid format.
You will also notice that often there are lots of the same mistakes, e.g misspellings of gmail, or hotmail.
By creating simple update find and replace queries you can clean up a good portion of the addresses. (if you need a hand with queries to do this let us know.)