Find The Number of Occurrences of a Character or a Substring in a 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 Substring in a 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 in a String'

SET @FindString = 'S'

SELECT (LEN(@SourceString) - LEN(REPLACE(@SourceString, @FindString, ''))) / LEN(@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)

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

If you know of a better option, then leave a comment below with your answers.

Readers Feedback:

Did you like this article? If Yes, then please share it: | More Options

Did you like my blog? If Yes, then please help spread the word by sharing this blog with your colleagues, friends, & anyone else in the MSBI Space: Like us on Facebook , Follow us on Twitter .