Find The Number of Occurrences of a Character or a String in Another String

Here is one of the common questions asked in many interviews but unfortunately many candidates fail to answer to this question. The question is "How to find the Number of Occurrences of a Character in a String in SQL Server" or "How to find the Number of Occurrences of a String in another String in SQL Server".

Below is a simple query which can answer both the above questions.

Find the Number of Occurrences of a Character in a String using T-SQL
Query:

DECLARE @SourceString VARCHAR(100)
DECLARE @FindString VARCHAR(10)

SET @SourceString = 'Find Number of Occurrences of a Character or a String in Another String'
SET @FindString = 'S'

SELECT (LEN(@SourceString) - LEN(REPLACE(@SourceString, @FindString, ''))) / LEN(@FindString) AS NumberOfOccurrences

Output:

Find the Number of Occurrences of a String in another String using T-SQL

Query:
DECLARE @SourceString VARCHAR(100)
DECLARE @FindString VARCHAR(10)

SET @SourceString = 'Find Number of Occurrences of a Character or a String in Another String'
SET @FindString = 'of'

SELECT (LEN(@SourceString) - LEN(REPLACE(@SourceString, @FindString, ''))) / LEN(@FindString) AS NumberOfOccurrences

Output:

As you can see the same query works for both finding the number of occurrences of a character and also a string inside another string.