Access Email Domain Query

Working for an email database company, I spend quite a bit of time using Microsoft Access and over the past three years I’ve learned quite a few useful tricks. One such query I run from time to time is stripping out the domain for an email address. Lets say you have a field in Microsoft Access called EMAIL and you want to strip the domain out from that, you could run the following query:

SELECT EMAIL, Right([EMAIL],Len([EMAIL])-InStr([EMAIL],"@")) as Domain
FROM TableName

That’s it, pretty simple. Read up on the Right() function here. In our case, the first parameter in the function will be the [EMAIL] field. The second parameter is doing two things, first we take the length of the [EMAIL] string by calling the Len() function and then subtract the position of the “@” sign in the email address.
So, for user@hotmail.com the query would literally translate as follows:

  • Right(“user@hotmail.com”, Len(“user@hotmail.com”)-InStr(“user@hotmail.com”,”@”))
  • Right(“user@hotmail.com”,16-5)
  • Right(“user@hotmail.com,11)
  • hotmail.com (hotmail.com is returned because they are the 11 rightmost characters in “user@hotmail.com”)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: