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))
ORDER BY EmailCount DESC

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.

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)

About these ads

21 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. you can also use the substring function to get the domain part of the email
    substring(email, charindex(‘@’, email) + 1, len(email))

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

    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

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

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

    Thanks

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

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

    (left([EMailAddress],charindex(‘@’,[EMailAddress])-(1)))
    and
    (right([EMailAddress],len([EMailAddress])-charindex(‘@’,[EMailAddress])))

    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.

  8. select SUBSTRING(EmailAddress,PATINDEX(‘%@%’,EmailAddress)+1,100),COUNT(1) from Person.Contact
    GROUP BY SUBSTRING(EmailAddress,PATINDEX(‘%@%’,EmailAddress)+1,100)
    ORDER BY 2 DESC

    Try This query.

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

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

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

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

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

  13. How to get sub string from a string:
    input:-
    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 ‘_’

    Thanks,
    Rashmita

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

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

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