Count the occurance of a character in a string

shax


Hi,
can anyone tell me how to count the occurance of a character wihtin a string

e.g. if the string were 'abcdeaaa' how would you count the occurances of 'a' i.e. 4 for the given string

hope you can help

cheers

NigelRivett

select len(@s) - len(replace(@s, 'a',''))

rusag2

Freakin' brilliant.

Anith Sen

Just for variety and fun, you can also use a Number table ( www.projectdmx.com/tsql/tblnumbers.aspx ) and do:

SELECT COUNT(*)

FROM Nbrs

WHERE SUBSTRING( 'abcdeaaa', n, 1 ) = 'a' ;

--

Anith

Chris Howarth

Nice approach, but use it with caution as it will fall over in the following scenario:

SET @s = 'adjkndjkna a'

The space character will not be included when LEN(REPLACE(@s, 'a','')) is calculated, leading to an inaccurate result.

It might be an idea to use something like this:

LEN(REPLACE(REPLACE(@s, ' ', '~'), 'a',''))

...to replace any spaces with a different character.

Chris