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

Question

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

Example: I want to find the number of times the letter ‘d’ appears in this string.

declare @string varchar(100)
select @string = 'sfdasadhfasjfdlsajflsadsadsdadsa'

Answer 1

Here you go:

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

Answer 2

Thanks for the solution.

Answer 3

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.