At SubMain we are collecting email addresses of the users who download trial version of our CodeIt.Right product as well as our free products - GhostDoc and .NET Coding Guidelines. We do that for two main reasons - we track download statistics and keep a point of contact with the users to provide them tips on using the products and special offers when become available.

As one can imagine some people do not use their actual email address and we understand that - there is too much email spam going on nowadays and people just don’t trust anyone anymore. As a result, we use multiple validation techniques to avoid our mail bouncing on the non existing accounts, including validating email addresses against the target mail server. But before we hit the actual servers, we do our home work and remove the addresses that we know do not exist, like those with “spam”, “junk”, etc in the email or the various temporary mail services like 10MinuteMail et al.

I used to use the following code as part of a stored procedure:

-- Mark junk/temporary email addresses
UPDATE #temp_Addresses SET Exclude = Exclude + 'Trash;'
WHERE (
       (Email LIKE '%spam%') OR (Email LIKE '%junk%') 
    OR (Email LIKE '%trash%') OR (Email LIKE '%fake%')
    OR (Email LIKE '%mailinator.com') OR (Email LIKE '%dodgeit.com') 
    OR (Email LIKE '%10minutemail.com') OR (Email LIKE '%mytrashmail.com')
    ...
)

(I actually reduced the /fairly long/ list to keep the post shorter and and kid friendly :)

Over time we re-used that code snippet in multiple places and when once again I needed to add new service to the hard-coded list in multiple places, I decided it is time to move the list into a table.

Well, while it would be nice to have LIKE IN (SELECT ... FROM ...) but it is not part of the SQL spec (yet?). So what do we do? We use a JOIN!

Here is sample data in the table DisposableEmailPatterns where we keep the list of the LIKEs above

Pattern
------------------------------
%spam%
%junk%
%dodgeit.com
%mailinator.com

And sample data in the temporary table #temp_Addresses

Email
------------------------------
mymail@mycompany.com
mylo@junk.com
temp12234@dodgeit.com
spam@mymail.net
mailbox12@mycorp.com
someone@hello.com

When we join the two tables above using the LIKE operator

SELECT t.Email, p.Pattern
  FROM #temp_Addresses t JOIN DisposableEmailPatterns p
    ON t.Email LIKE p.Pattern

We get the end result that is exactly the same as our long hard coded

Email                          Pattern
------------------------------ ------------------------------
spam@mymail.net                %spam%
mylo@junk.com                  %junk%
temp12234@dodgeit.com          %dodgeit.com

And that’s it, we have our solution, powerful yet elegant! :)

Later!