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.

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)

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

Menu