Example
Suppose, I have a string 'Amit Kumar' and want to find the occurance of A and M in this string. Find the length of 'Amit Kumar',
  1. Declare @String nvarchar(20) ='Amit Kumar'
Full query
  1. select len(@String )-len (replace(@String ,'a','')) as OccuranceOfA
Output
Explanation

Get total length of the given string
  1. select len(@string) as TotalLength
Output


Replace a with ' ', then get the length(removing 'A')
  1. select len (replace(@String ,'a','')) as RemovingA
Output



Occurrence= TotalLength-RemovingA
= 10-8
Final output


Query for 'M'
  1. select len(@String )-len (replace(@String ,'M','')) as OccurrenceOfM
Output