Count number of occurancies

Question

Count number of occurancies

bhushanhegde

Old Hand

Points: 382
More actions
December 18, 2008 at 1:30 am

#195235

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

bhushanhegde (12/18/2008)
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.

What have you tried so far?

How do you want the result - as a variable, a column in a result set?

How often are you expecting to do this?

Is it just once - I can count 6 by eye - or do you want to perform a similar operation many times, perhaps finding 'h' or '9' instead of 'a'?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bhushanhegde Old Hand Points: 382 More actions · Answer 2

Hi,

It should be done prqently.

This kind of code i want in my procedure.

I will pass some data to a variable and in that variable i want find oput a perticular characte(~) occured how many times?

means something like substring, charindex like that we have any function?

my code will be as follows:

set @cntr=function_name('~',var1)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Thank you for the prompt answers.

There are several ways to do this, my preference would be a function using a tally-table - are you permitted to add a utility table to your db?

Here's [/url]a link to tally tables.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bhushanhegde Old Hand Points: 382 More actions · Answer 4

Hi,

I dont have rights to create any objects in the data base since that is in production.

I want to do with any sys defined functions....is it possible?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

bhushanhegde (12/18/2008)
Hi,
I dont have rights to create any objects in the data base since that is in production.
I want to do with any sys defined functions....is it possible?

Yes.

DECLARE @TargetString VARCHAR(100), @SearchString VARCHAR(5)

SET @SearchString = 'a'

SET @TargetString = 'hurrycanecathurrycanecatand dod race'

SET @TargetString = LTRIM(@TargetString)

SELECT LEN(@TargetString) - LEN(REPLACE(@TargetString, @SearchString, ''))

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

bhushanhegde (12/18/2008)
Hi,
I dont have rights to create any objects in the data base since that is in production.
I want to do with any sys defined functions....is it possible?

So...does it work?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 7

Also asked and answered here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=116495

N 56°04'39.16"
E 12°55'05.25"