Count how many times a character appeared in a string

Hi all,
I'm having trouble googling this problem ...
Would anyone know the easiest way to obtain the number of times a
character appeared in a given string?
Thanks
ACA WHILE loop will do the trick. Just for fun though, here's a set-based
approach that uses the good old numbers table:
SELECT TOP 100 number = IDENTITY(INT, 1, 1)
INTO #numbers
FROM syscomments a1
CROSS JOIN syscomments a2
ALTER TABLE #numbers
ADD CONSTRAINT pk_number
PRIMARY KEY CLUSTERED (number)
DECLARE @.string VARCHAR(20)
DECLARE @.letter CHAR(1)
SELECT @.string = 'THIS IS A TEST'
SELECT @.letter = 'S'
SELECT @.letter AS letter, COUNT(*) AS occurrences
FROM #numbers n
WHERE SUBSTRING(@.string, n.number, 1) = @.letter
DROP TABLE #numbers
"AC" <anchi.chen@.gmail.com> wrote in message
news:1150341230.216963.54010@.g10g2000cwb.googlegroups.com...
> Hi all,
> I'm having trouble googling this problem ...
> Would anyone know the easiest way to obtain the number of times a
> character appeared in a given string?
>
> Thanks
> AC
>|||Thanks Mike. I'll give that a try.
Thanks again.
AC|||DECLARE @.foo VARCHAR(64);
DECLARE @.c VARCHAR(1);
SET @.foo = 'How many qs are in this qqq qqq blat?';
SET @.c = 'q';
SELECT Number_Of_Qs = LEN(@.foo) - LEN(REPLACE(@.foo, @.c, ''));
"AC" <anchi.chen@.gmail.com> wrote in message
news:1150341230.216963.54010@.g10g2000cwb.googlegroups.com...
> Hi all,
> I'm having trouble googling this problem ...
> Would anyone know the easiest way to obtain the number of times a
> character appeared in a given string?
>
> Thanks
> AC
>|||This is such a smart and short solution.
Thanks Aaron
Aaron Bertrand [SQL Server MVP] wrote:

> DECLARE @.foo VARCHAR(64);
> DECLARE @.c VARCHAR(1);
> SET @.foo = 'How many qs are in this qqq qqq blat?';
> SET @.c = 'q';
> SELECT Number_Of_Qs = LEN(@.foo) - LEN(REPLACE(@.foo, @.c, ''));
>
>