Sql-Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sql-Server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-11-22 : 07:25:23

Deep writes "Sir/Madam
I want to pick only one email-id per domain.

for e.g Table -> Subscribe
Field -> email
Data : abc@hotmail.com
xyz@hotmail.com
abc@yahoo.com
xyz@yahoo.com

output : abc@hotmail.com
abc@yahoo.com

please send the query"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-22 : 07:31:40

something like

select distinct data
from Subscribe t1
where data = (select top 1 t2.data from subscribe t2 where right(t1.data, charindex('@',reverse(t1.data))-1) = right(t2.data, charindex('@',reverse(t2.data))-1))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-22 : 07:33:25


declare @Subscribe table (email varchar(50))
insert into @Subscribe
select 'abc@hotmail.com' union all
select 'xyz@hotmail.com' union all
select 'abc@yahoo.com' union all
select 'xyz@yahoo.com'

select 	min(email)
from 	@Subscribe 
group by substring(email, charindex('@', email), len(email))

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources