Occurrence Of Particular Character In String In SQL

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

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now