occurences of a character

How can I get a count of occurences of a specific character in a string?By using the substring, charindex, and datalength functions, along with a position_in_string placeholder, a counter to increment for each occurrence you find, and an end_of_string indicator. You will also need to use a while loop.

You put the statements together, and then come back for a review.|||Not sure how to put all of that together.

By using the substring, charindex, and datalength functions, along with a position_in_string placeholder, a counter to increment for each occurrence you find, and an end_of_string indicator. You will also need to use a while loop.

You put the statements together, and then come back for a review.|||omg plz, not a while loop

ur doing it wrong (http://www.google.com/search?q=%22ur+doing+it+wrong%22+%2Blolcats) ;)

select len(str) -
len(replace(str,'x',''))
as number_of_xs
:)|||Thanks, that is doing it. I have never been very good at loops, and try to avoid them. Knew there has to be a quick way to do this.|||omg plz, not a while loop

ur doing it wrong (http://www.google.com/search?q=%22ur+doing+it+wrong%22+%2Blolcats) ;)


select len(str) -
len(replace(str,'x',''))
as number_of_xs
:)

naw ... not wrong ... just misguided. String character inspection was the first thing that came to mind. I obviously like your code much better than the cobbled contraption mine would be. And it would even work as a set based solution.

Thanks rudy - this one goes into the toolkit!|||Una caveat...

Be aware that LEN does not count any trailing blanks, so if that possibility exists (as in this example, when there is a blank and then a couple of the target characters), you'll have to code to handle it...
DECLARE @.str varchar(24)

SET @.str = 'xx xuxs ssx xx'

select @.str as OrigStr,
len(@.str) as lenOfOrigStr,
'!' + replace(@.str,'x','') + '!' as XLessStr,
len(replace(@.str,'x','')) as LenOfXLessStr,
len(@.str) - len(replace(@.str,'x','')) as number_of_xs

OrigStr lenOfOrigStr XLessStr LenOfXLessStr number_of_xs
----- ---- ---- ---- ---- ----
xx xuxs ssx xx 14 ! us ss ! 6 8 Mind you, I found this by accident, but I R shtill shmart.

might I suggest something cludgy, like:select len(@.str) - len(replace((@.str + '!'),'x','')) + 1 as number_of_xs