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

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

Reference: Pinal Dave (http://blog.SQLAuthority.com)

28 thoughts on “SQL SERVER – Selecting Domain from Email Address

  1. SELECT RIGHT(Email, CHARINDEX(‘@’, REVERSE(email))-1) as Domain ,
    COUNT(Email) as EmailCount
    FROM dbo.email
    WHERE LEN(Email) > 0
    GROUP BY RIGHT(Email, CHARINDEX(‘@’, REVERSE(email))-1)
    ORDER BY EmailCount DESC


  2. Cant we use “substring” instead if “right” here ?

    select substring(Email , CHARINDEX(‘@’, Email )+ 1,len(Email)) Domain ,
    count(Email ) EmailCount
    from dbo.data
    where len(isnull(Email ,”)) > 0
    group by substring(Email , CHARINDEX(‘@’, Email )+ 1,len(Email))
    order by Emailcount desc

    Liked by 1 person

  3. select substring(emailid,charindex(‘@’,emailid)+1,len(emailid)) as Domain,count(emailid) as EmailCount
    from email
    where len(emailid)>0
    group by substring(emailid,charindex(‘@’,emailid)+1,len(emailid))
    order by Emailcount desc


  4. Hi Pinal,

    Recently I have used the same method for one of my shopping cart project. I was asked to show approx 50 characters for a product description followed by 4 dots and it should not cut last word. So I used the same method. here is my query

    SELECT productID, productName, LEFT(productDescription, CHARINDEX(” ”, productDescription, 50))+”….” as productDescription, ….
    FROM tbl_Products



  5. According to RFC’s 5322 and 5321, a quoted string can be used to allow invalid characters… such that “pinal@home”@sqlauthority.com would be a valid email address. However, it’s rare to see a quoted string used for email.


  6. I have something like that. Which was separating the username and domain parts of the address. I am using it in a computed column. See below


    However I was not able to solve a problem with it crashing when reading the table with invalid email’s which did not contain an @ symbol.


  7. SELECT ISNULL(SUBSTRING(Email,CHARINDEX(‘@’,isnull(email,”)) + 1,LEN(Email) – CHARINDEX(‘@’,isnull(email,”))),”),COUNT(*) as overallcount
    FROM @t
    WHERE ISNULL(Email,”) ”
    ISNULL(SUBSTRING(Email,CHARINDEX(‘@’,isnull(email,”)) + 1,LEN(Email) – CHARINDEX(‘@’,isnull(email,”))),”)
    overallcount DESC


  8. Hi Pinal,
    With minimal operation I think we can achieve this with below code and it can handle multiple @ scenario

    RIGHT(email,CHARINDEX(‘@’,REVERSE(email))-1) AS Domain,
    COUNT(*) AS [No]
    FROM @t
    WHERE len(ISNULL(email,”)) > 1
    GROUP BY RIGHT(email,CHARINDEX(‘@’,REVERSE(email))-1)


  9. Pingback: SQLAuthority News – Monthly list of Puzzles and Solutions on SQLAuthority.com Journey to SQLAuthority

  10. Hi Pinal,

    we need add email validation check in this script because this script is working on @ but some invalid email entered in database table then this scriot is not working as expected.

    I have modified it as below.

    SELECT RIGHT(Email, LEN(Email) – CHARINDEX(‘@’, email)) Domain ,
    COUNT(Email) EmailCount
    FROM dbo.email
    WHERE LEN(Email) > 0 and CHARINDEX(‘@’, email) > 0
    GROUP BY RIGHT(Email, LEN(Email) – CHARINDEX(‘@’, email))
    ORDER BY EmailCount DESC


  11. How to get sub string from a string:
    1. /documents/subDocuments/test_123.txt (required result ‘test’)
    2. /documents/subDocuments/test_def_45ddd.txt (required result ‘test_def’)

    I want the sub string between last index of ‘/’ and last index of ‘_’



  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #034 | Journey to SQL Authority with Pinal Dave

  13. Hi, Thanks for reply.. For MySQL query will be like

    SELECT RIGHT(email, length(email) – length(SUBSTRING_INDEX(email,’@’,1))) as Domain, COUNT(email) as Cnt
    FROM table
    GROUP BY RIGHT(email, length(email) – length(SUBSTRING_INDEX(email,’@’,1)))
    ORDER BY Cnt


  14. You can use the below in oracle to retrieve only company name like ‘yahoo’ or ‘gmail’ etc., from email id Hence it will omit .com or .co.in in the end.

    Let me know if this is helpful for you guys, your feedback is highly appreciated.

    select substr(‘Email’,instr(‘Email’,’@’)+1,(instr(‘Email’,’.c’,-1)-1)-(instr(‘Email’,’@’))) from Table Name


  15. Pingback: EXCEL / SQL SERVER – Extract the Domain from an Email Address | Journey to SQL Authority with Pinal Dave

  16. 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.



  17. Hint:
    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


  18. 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


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