number of occurances? - 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

SQL Server Development (2000)

number of occurances?

Author

Topic

blackX
Posting Yak Master

102 Posts

Posted - 2010-02-19 : 13:40:53

I would like to add a derived column to my query. I would like to count the number of times a pattern ocurs in a field, for each record produced by the select statement.

Pretend this is my table

col1 col2 col3
a b cc
a d ccc

and I want to say
select "number times c appears in col3 for each record" from table

desired output is

row1 - 2 occurances of c
row2 - 3 occurances of c

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 14:03:20

[code]SELECT *, LEN(col3) - LEN(REPLACE(col3,'c','')) AS [occuranceofc] FROM table[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

blackX
Posting Yak Master

102 Posts

Posted - 2010-02-19 : 14:39:20

that works...almost

what if 'c' was a string not just a single character?

actually what I really need to do is count the number of times the current date appears in a notes field.

The notes are timestamped with each call that is made.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-19 : 15:13:41

What's the definition of the notes field?

varchar(max)?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-19 : 15:37:57

maybe this?

declare @t table
(col1 int,col2 int,Notes varchar(200))
insert @t
select 1,2,'20100219-test,20100219-test1,20090218-test3'
union all select 1,3,'20100219-test'
union all select 1,5,'20100218-test'

SELECT *, datalength(Notes) - datalength(REPLACE(Notes,'20100219',replicate(' ',len('20100219') - 1))) AS [occuranceofc] FROM @t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 00:18:44

quote:
Originally posted by blackX

that works...almost

what if 'c' was a string not just a single character?

actually what I really need to do is count the number of times the current date appears in a notes field.

The notes are timestamped with each call that is made.

what do you mean by that?
it works even for a string

see


SELECT LEN(val)-LEN(REPLACE(val,'c','')) AS [Occuranceofc]
FROM 
(
SELECT 'ccclojfeel nsDSZZcc kkxdlkwqcccccbmqxxc' as val
)t

output
------------------------------
Occuranceofc
11

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

blackX
Posting Yak Master

102 Posts

Posted - 2010-02-24 : 15:52:36

Correct, it works for the string but the count is the number of characters replaced not the occurances of the string. So if i had the following

AABBCCDDAABBCC and was searching for the number of occurnces AABB appeared the desired result would be 2. But the code provided would return 4 because the string to search would be 4 characters less in length if we replaced AABB with nothing.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-02-24 : 15:56:56

The other solution posted works for u...no?

declare @t table
(col1 int,col2 int,Notes varchar(200))
insert @t
select 1,2,'AABBCCDDAABBCC-test,20100219-test1,20090218-test3'

SELECT *, datalength(Notes) - datalength(REPLACE(Notes,'AABB',replicate(' ',len('AABB') - 1))) AS [occuranceofc] FROM @t

gives result as '2'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources