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
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 email@example.com the query would literally translate as follows:
- Right(“firstname.lastname@example.org”, Len(“email@example.com”)-InStr(“firstname.lastname@example.org”,”@”))
- hotmail.com (hotmail.com is returned because they are the 11 rightmost characters in “email@example.com”)