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”)
