No Of occurancies - 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)

No Of occurancies

Author

Topic

bhushanhegde
Starting Member

14 Posts

Posted - 2008-12-18 : 03:39:18

Hi,
My string is as follows:

'hurrycanecathurrycanecatand dod race'

I want to find out character 'a' how many times occured in the above string.

pls help me.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 03:49:49

DECLARE @a VARCHAR(100)
SET @a = 'hurrycanecathurrycanecatand dod race'

SELECT LEN(@a) - LEN(REPLACE(@a, 'a', ''))

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 03:50:36

Also see http://weblogs.sqlteam.com/peterl/archive/2008/10/27/Sequencies-in-string.aspx

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

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-18 : 03:53:44

DECLARE @cnt INT,@start INT
SELECT @cnt = 0,@start =1
DECLARE @len INT
SELECT @len = len('hurrycanecathurrycanecatand dod race')
WHILE(@len>0)
BEGIN
IF (left(substring('hurrycanecathurrycanecatand dod race',@start,len('hurrycanecathurrycanecatand dod race')
),1) = 'a')
BEGIN
SELECT @cnt = @cnt +1
END
SELECT @len = @len - 1
SELECT @start = @start + 1
END
SELECT @cnt

Jai Krishna

bhushanhegde
Starting Member

14 Posts

Posted - 2008-12-18 : 03:55:33

Hi,
is there any sys defined function to do that? like we have
substring, charindex

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-18 : 03:59:48

To my Knowledge I have not found any sys function to solve your problem

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 05:53:40

See post made 12/18/2008 : 03:49:49
There is no need for a loop...

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

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-18 : 06:15:01

DECLARE @a VARCHAR(100)
SET @a = 'hurrycanecathurrycanecatand dod aaaa'

SELECT LEN(@a) - LEN(REPLACE(@a, 'a', ''))

Not Getting Correct o/p for the above i/p

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-18 : 06:25:00

Then replace LEN with DATALENGTH...

DECLARE @a VARCHAR(100)
SET @a = 'hurrycanecathurrycanecatand dod aaaa'

SELECT DATALENGTH(@a) - DATALENGTH(REPLACE(@a, 'a', ''))

And you still don't need a loop.

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

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-18 : 06:27:57

Its Ok..

I just tried differently from urs

Jai Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources