SQL Server get number of occurrences of a character in a string

In SQL Server its a common requirement to count how many times a particular character is appeared in a string

in “Hello friend” , “l” occurred 2 times 

To find the number of occurrences , lets use a logic by

1. Take total length of the string , say its 12 , in our case
2. Now remove all “l” s
3. Find difference before and after removing “l”
4. This difference is the number of occurrence of “l” in the string “Hello friend”

Example

select len('Hello friend')
12
select replace('Hello friend','l','')
10
select len('Hello friend') - len(replace('Hello friend','l',''))
2