How can you find the number of occurrences of a particular character in a string using sql?

Here you go:

declare @string varchar(100)
select @string = 'sfdasadhfasjfdlsajflsadsadsdadsa'
SELECT LEN(@string) - LEN(REPLACE(@string, 'd', '')) AS D_Count

If you want to make it a little more general, you should divide by the length of the thing you're looking for. Like this:

declare @searchstring varchar(10);
set @searchstring = 'Rob';

select original_string, 
(len(orginal_string) - len(replace(original_string, @searchstring, '')) 
   / len(@searchstring)
from someTable;

This is because each time you find 'Rob', you remove three characters. So when you remove six characters, you've found 'Rob' twice.