SQL SERVER – Selecting Domain from Email Address

Recently I came across a quick need where I needed to retrieve domain of the email address. The email address is in the database table. I quickly wrote following script which will extract the domain and will also count how many email addresses are there with the same domain address.

SELECT RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) Domain ,
COUNT(Email) EmailCount
FROM   dbo.email
WHERE  LEN(Email) > 0
GROUP BY RIGHT(Email, LEN(Email) - CHARINDEX('@', email))

Above script will select the domain after @ character. Please note, if there is more than one @ character in the email, this script will not work as that email address is already invalid.

SQL SERVER - Selecting Domain from Email Address emailaddress

Do you have any similar script which can do the same thing efficiently? Please post as a comment.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts
Previous Post
SQL SERVER – Solution – Puzzle – Statistics are not Updated but are Created Once
Next Post
SQLAuthority News – Download Whitepaper Using SharePoint List Data in PowerPivot

Related Posts

34 Comments. Leave new

  • I would like to set the domain formula in a defaut value for a new column … but it seems not to be possible …

  • Hi There,

    I have a requirement: Find and extract email address from Long Text field. The field contains more than 2000 chars. I am looking for ordinary SQL Query (TSQL), so i can use it in Business Intelligence Report.

    I can’t use ‘Function’ or any other format.s

    I am searching in Google not able to find the right method.

    Thanks for your help.


  • Konto Erstellen
    November 16, 2015 5:35 pm

    You can easily turn this query around to give you all the Email Adresses of a given colum, simply change the code a little bit to get the left string part instead of the right string part:

    SELECT LEFT(Col0, CHARINDEX(‘@’, Col0) ) as EmailColumn, Col0
    FROM MyTabe
    order by Col0

  • in asp access database this query not work, someone has the solution, for asp and access database thanks in advance.

    SQL = ” SELECT RIGHT(email, LEN(email) – CHARINDEX(‘@’, email)) AS Domain, COUNT(email) AS EmailCount FROM tbUser WHERE (LEN(email) > 0) GROUP BY RIGHT(emailUser, LEN(email) – CHARINDEX(‘@’, email)) ORDER BY EmailCount DESC

  • This was so helpful, thank you!

  • Hi, I have solved this in very clear way, no need to long query

    Select Id, email, right(email, charindex(‘@’, reverse(email))-1) as domain from domain

    Here I’d, email is a column name and table name is domain.

    Amit pratap

  • select reverse(substring(reverse(email),1,charindex(‘@’,reverse(email))-1)),count(*) as count
    from membersinfo
    group by reverse(substring(reverse(email),1,charindex(‘@’,reverse(email))-1))
    order by count(*) desc;

  • Hi Sir can you please simplify my query to get domain name without .com

    select email,
    substring(email,charindex(‘@’,email)+1,LEN(Email) – CHARINDEX(‘@’, email)),
    substring(email,charindex(‘@’,email)+1,LEN(Email) – CHARINDEX(‘@’, email)),len(
    substring(email,charindex(‘.’,email)+1,LEN(Email) – CHARINDEX(‘.’, email))
    replace(substring(email,charindex(‘@’,email)+1,LEN(Email) – CHARINDEX(‘@’, email)),
    substring(email,charindex(‘@’,email)+1,LEN(Email) – CHARINDEX(‘@’, email)),len(
    substring(email,charindex(‘.’,email)+1,LEN(Email) – CHARINDEX(‘.’, email))


    • select substring(‘hemasundar@gmail.com’,charindex(‘@’,’hemasundar@gmail.com’)+1,charindex(‘.,’,hemasundar@gmail.com’)

      —-this method will you to get only domain name from given email

  • thank you so much indeed nice query works perfect

  • Yes, thanks this query did exactly what I needed. Pinal Dave is the Man!

  • I have a column in a SQL table that has 5000 email addresses in it. I created two new columns called Username and Domain. I would like to split that email address at the @ writing into the new column the prefix into Username and the suffix into Domain. I cannot find an example of how to do this. Your help would be appreciated.


Leave a Reply