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

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 in a String’

SET @FindString = ‘S’

SELECT (LEN(@SourceString) – LEN(REPLACE(@SourceString, @FindString, ”)))  AS NumberOfOccurrences

Output:

NumberOfOccurrences

——————-
2
(1 row(s) affected)

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

Query:
DECLARE @SourceString VARCHAR(100)
DECLARE @FindString VARCHAR(10)
SET @SourceString = ‘Find Number of Occurrences of a Substring in a String’
SET @FindString = ‘of’
SELECT (LEN(@SourceString) – LEN(REPLACE(@SourceString, @FindString, ”))) / LEN(@FindString) AS NumberOfOccurrences
Output:

NumberOfOccurrences

——————-

2

(1 row(s) affected)