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 (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 …

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

    Kiru

    Reply
  • Konto Erstellen
    November 16, 2015 5:35 pm

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

    Reply
  • 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

    Reply
  • This was so helpful, thank you!

    Reply
  • amit pratap
    June 5, 2016 6:43 pm

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

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

    Reply
  • 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;

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

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

    Thanks,
    Vani

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

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

      Reply
  • thank you so much indeed nice query works perfect

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

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

    Reply

Leave a ReplyCancel reply

Exit mobile version