Occurrence Of Particular Character In String In SQL

In this blog, we will calculate occurrence of a particular character in the string.

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',

Declare @String nvarchar(20) ='Amit Kumar'

Full query

select len(@String )-len (replace(@String ,'a','')) as OccuranceOfA

Output

 

 

Explanation

Get total length of the given string

select len(@string) as TotalLength

Output

 

 

Replace a with ' ', then get the length(removing 'A') 

select len (replace(@String ,'a','')) as RemovingA

Output



Occurrence= TotalLength-RemovingA 

                  = 10-8

 

Final output

 

Query for 'M'  

select len(@String )-len (replace(@String ,'M','')) as OccurrenceOfM

Output