SQL SERVER – Selecting Domain from Email Address
Posted
by pinaldave
on SQL Authority
See other posts from SQL Authority
or by pinaldave
Published on Sat, 18 Jun 2011 01:30:08 +0000
Indexed on
2011/06/20
16:27 UTC
Read the original article
Hit count: 468
Pinal Dave
|PostADay
|sql
|SQL Authority
|SQL Query
|SQL Scripts
|SQL Server
|SQL Tips and Tricks
|T SQL
|Technology
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)
Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology
© SQL Authority or respective owner
