Split string and then run count

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 2005 Forums

Transact-SQL (2005)

Split string and then run count

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2009-06-15 : 15:50:22

Hi,

I've got this data

a@yahoo.co.uk
b@yahoo.co.uk
c@yahoo.co.uk
a@hotmail.com
b@hotmail.com

I want to count the unique domain names, so for my results I want

yahoo.co.uk 3
hotmail.com 2

How can I do this?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-15 : 15:52:16

SELECT SUBSTRING(Col1, CHARINDEX('@', Col1) + 1, LEN(Col1)) AS Domain, COUNT(*)
FROM Table1
GROUP BY SUBSTRING(Col1, CHARINDEX('@', Col1) + 1, LEN(Col1))

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources