EXCEL / SQL SERVER – Extract the Domain from an Email Address

Just like any business person, I work with Excel pretty much half of my time when I am not working with SQL Server. Recently I faced challenges when I wanted to extract domain from the email address. I was using excel. I was in extreme rush so I did something which was in fact actually longer route than easier way. I laughed at myself and decided to blog about it. Here is how I got the domain from an email address, and the shortcut I missed.

Here is the way I took to extract domain out of email in excel.

1. Imported Excel to SQL Server Table
2. Executed script from my blog post to extract domain from email in SQL Server
3. Exported SQL Server Table to Excel

In reality, I should just have written a new function in different columns of excel which can extract domain out of Excel.

Here is the function which will extract domain from the email address.

=MID(B2,SEARCH(“@”,B2)+1,255)

Following is the image which displays how above simple excel function can return the domain from the email address.

Handling Messy Data When You Pull the Domain from an Email Address

Real email lists are rarely clean, so plan for the odd rows. In Excel, if a cell is empty or has no @ sign, the formula returns a #VALUE! error. Wrapping it as =IFERROR(MID(B2,SEARCH("@",B2)+1,255),"") keeps the column tidy. If you use a recent version of Excel, TEXTAFTER(B2,"@") does the same job with a shorter formula.

In SQL Server, the usual pattern is SUBSTRING(Email, CHARINDEX('@', Email) + 1, LEN(Email)). Be careful here: when there is no @, CHARINDEX returns 0 and the expression gives back the whole value, not an empty string. Add a WHERE Email LIKE '%@%' filter or a CASE expression to catch those rows.

In both tools, trim the spaces and change the result to lower case before you count or group by domain, or the same company may show up twice. A quick count by domain afterwards is also a nice way to spot typos such as gmial.com in the list.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.