string expression occurance (easy)

[youngun]

Is there any built-in function for searching the number of times a particular expression happens in a string?

For example, I want to know how many times 'D' happens in 'DADDY'.

Thanks a lot.

 

[redlam]

i don't think there is a built in function but you can create your own in sql2000
try this:
create function strCount (@lookfor char(1), @lookin varchar(20))
returns int
as
begin
declare @position int
set @position = 1
declare @count int
set @count = 0
while @position between 1 and len(@lookin)
begin
set @position = charindex(@lookfor,@lookin,@position)
if @position <> 0
begin
set @position = @position + 1
set @count = @count + 1
end
end

return @count
end

then call it like this:
select dbo.strCount('d','daddy')