SQL SERVER – Selecting Domain from Email Address

Following script which will extract the domain and will also count how many email addresses are there with the same domain address.



declare @t table(email varchar(100))
insert into @t
select 'test@yahoo.com' union all
select 'test@msn.com' union all
select 'test@gmail.com' union all
select 'test1@yahoo.com' union all
select 'test@sify.com' union all
select 'test2@yahoo.com' union all
select 'test3@msn.com' union all
select 'test4' union all
select '' union all
select null union all
select 'test1@gmail.com'


Method #1


select
stuff(email,1,charindex('@',email),'') as domain,
count(*) as emailcount
from
@t
where 
email > ''
group by
stuff(email,1,charindex('@',email),'')
order by
emailcount desc


Method #2


SELECT RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) Domain ,
COUNT(Email) EmailCount
FROM   @t
WHERE  LEN(Email) > 0
GROUP BY RIGHT(Email, LEN(Email) - CHARINDEX('@', email))
ORDER BY EmailCount DESC


SELECT RIGHT(Email, LEN(Email) - CHARINDEX('@', email)) Domain ,
COUNT(Email) EmailCount
FROM @t
WHERE LEN(Email) > 0 and CHARINDEX('@', email) > 0
GROUP BY RIGHT(Email, LEN(Email) - CHARINDEX('@', email))
ORDER BY EmailCount DESC


Method #3


;WITH cte AS (
    SELECT CAST(
    '<i>' + REPLACE(email, '@', '</i><i>') + '</i>'
    AS XML).value('/i[2]', 'varchar(50)') AS Domain
    FROM @t 
)
SELECT
    Domain, 
    COUNT(*) AS Cnt
FROM cte
WHERE Domain IS NOT NULL
GROUP BY Domain 
ORDER BY COUNT(*) DESC


Method #4


select
substring(email,charindex('@',email)+1,len(email)) as domain,
count(*) as emailcount
from
@t
where
email>''
group by
substring(email,charindex('@',email)+1,len(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.


domain emailcount
yahoo.com 3
gmail.com 2
msn.com 2
sify.com 1

Different ways to know structure of a table

Generate SQL Script option from Enterprise Manager/Management Studio


select * from information_schema.columns where table_name='table_name'


EXEC sp_help 'table_name'


EXEC sp_columns 'table_name'


In Query Analyser type the name of the table, highlight it and press Alt+F1