how to count - 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 2005 Forums

Transact-SQL (2005)

how to count

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-01-31 : 17:05:09

Can anyone have advice, how to get below output? I need to count number and display where col2 not like '%@abc.com' and col not like '%@xyz.com' domain emails form below data.

Declare @tbl1 table
(Col1 INT,
Col2 varchar (255)
)

INSERT INTO @tbl1
SELECT 1, 'aa@abc.com;bb@abc.com;abc@xyz.com;abc@mno.com;bb@xyz.com;abc@123.com'

INSERT INTO @tbl1
SELECT 2, 'dd@abc.com;mm@abc.com;aa@abc.com;bb@abc.com;abc@xyz.com;abc@mno.com;bb@xyz.com;abc@123.com'

INSERT INTO @tbl1
SELECT 3, 'aa@abc.com;bb@abc.com;abc@xyz.com;abc@mno.com;bb@xyz.com;abc@123.com;dd@xyz.com;aaa@abc.com'

--select * from @tbl1

OUT PUT LOOKS LIKE THIS:
Col1 EmailsCount Col2
1 2 abc@mno.com;bb@xyz.com;abc@123.com
2 2 abc@mno.com;bb@xyz.com;abc@123.com
3 2 abc@mno.com;abc@123.com

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-31 : 17:16:56

Do you mean this?

select count(*) from table where mailColumn not like '%@abc.com' and mailColumn not like '%@xyz.com'

No, you're never too old to Yak'n'Roll if you're too young to die.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-01-31 : 18:36:16

Nope, the above email address are stored as single row, i have to count how many email names are there and have to list those email address too.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-31 : 18:51:02

One way to count:

SELECT 
	LEN(REPLACE(REPLACE(YourColumn,'@abc.com',''),'@xyz.com',''))
	-
	LEN(REPLACE(REPLACE(REPLACE(YourColumn,'@abc.com',''),'@xyz.com',''),'@',''));

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-31 : 19:00:13

or split them on the semi colon using a string splitting function.

Then you could count em or group em or anything else that takes your fancy.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-01-31 : 19:10:37

I have tried for these data, it does not work for me, please see my accepted out put on below.
Declare @tbl1 table
(Col1 INT,
Col2 varchar (255)
)

INSERT INTO @tbl1
SELECT 1, 'aa@abc.com;bb@abc.com;abc@xyz.com;abc@mno.com;bb@xyz.com;abc@123.com'

INSERT INTO @tbl1
SELECT 2, 'dd@abc.com;mm@abc.com;aa@abc.com;bb@abc.com;abc@xyz.com;abc@mno.com;bb@xyz.com;abc@123.com'

INSERT INTO @tbl1
SELECT 3, 'aa@abc.com;bb@abc.com;abc@xyz.com;abc@mno.com;bb@xyz.com;abc@123.com;dd@xyz.com;aaa@abc.com'

--select * from @tbl1

select LEN(REPLACE(REPLACE(col2,'@abc.com',''),'@xyz.com','')) from @tbl1
select LEN(REPLACE(REPLACE(REPLACE(col2,'@abc.com',''),'@xyz.com',''),'@','')) from @tbl1

OUT PUT LOOKS LIKE THIS:
Col1 EmailsCount Col2
1 2 abc@mno.com;bb@xyz.com;abc@123.com
2 2 abc@mno.com;bb@xyz.com;abc@123.com
3 2 abc@mno.com;abc@123.com

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-01-31 : 22:46:39

Can anyone advise how to get the solutions?

Declare @tbl1 table
(Col1 INT,
Col2 varchar (255)
)

INSERT INTO @tbl1
SELECT 1, 'aa@abc.com;bb@abc.com;abc@xyz.com;abc@mno.com;bb@xyz.com;abc@123.com'

INSERT INTO @tbl1
SELECT 2, 'dd@abc.com;mm@abc.com;aa@abc.com;bb@abc.com;abc@xyz.com;abc@mno.com;bb@xyz.com;abc@123.com'

INSERT INTO @tbl1
SELECT 3, 'aa@abc.com;bb@abc.com;abc@xyz.com;abc@mno.com;bb@xyz.com;abc@123.com;dd@xyz.com;aaa@abc.com'

--select * from @tbl1

OUT PUT LOOKS LIKE THIS:
Col1 EmailsCount Col2
1 2 abc@mno.com;bb@xyz.com;abc@123.com
2 2 abc@mno.com;bb@xyz.com;abc@123.com
3 2 abc@mno.com;abc@123.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-01 : 03:35:00

use a Split Function (you can get one from sqlteam.com)


select 	Col1,
	EmailsCount	= count(*),
	Col2
from 	@tbl1 t
	outer apply dbo.SPLIT_FUNCTION(';', Col2)
where	SplitCol	not like '%@abc.com'
and	SplitCol	not like '%@xyz.com'
group by Col1, Col2

here are some of the Split Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-02-01 : 09:47:17

i got count, how do we get those email names which is not in @abc.com and @xyz.com

in above data, out put should be like this:

1 2 abc@mno.com;bb@xyz.com;abc@123.com
2 2 abc@mno.com;bb@xyz.com;abc@123.com
3 2 abc@mno.com;abc@123.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-01 : 10:01:55

what do you want as output exactly ? the output that you shown does not matches your description. You said not in @xyz.com but in the output you included that

quote:
Originally posted by rudba

i got count, how do we get those email names which is not in @abc.com and @xyz.com

in above data, out put should be like this:

1 2 abc@mno.com;bb@xyz.com;abc@123.com
2 2 abc@mno.com;bb@xyz.com;abc@123.com
3 2 abc@mno.com;abc@123.com

KH
[spoiler]Time is always against us[/spoiler]

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-02-01 : 10:22:01

khtan, i have to exclude those email domain (@abc.com and @xyz.com) than count how many emails and have to pull those email address (not for @abc.com and @xyz.com)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-01 : 17:46:00

You can use KH's query, and take the results of the split function to concatenate the filtered e-mails - like this:

;WITH cte AS
(
	SELECT Col1,SplitCol
	FROM   @tbl1 t
		   OUTER APPLY dbo.SPLIT_FUNCTION(';', Col2)
	WHERE  SplitCol NOT LIKE '%@abc.com'
		   AND SplitCol NOT LIKE '%@xyz.com'
)
SELECT
	col1,
	COUNT(*),
	STUFF(Emails,1,1,'') AS FilteredEmails
FROM
	cte a
	CROSS APPLY 
	( SELECT DISTINCT 
		(SELECT ';'+b.SplitCol AS [text()] 
	                   FROM cte b WHERE a.col1=b.col1 FOR XML PATH('')
		)AS Emails 
	)s
GROUP BY
	col1, Emails;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources