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.
Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on November 26, 2010, in Code Snippets, Interview Questions, SQL Server, T-SQL, T-SQL Questions, T-SQL Snippets, T-SQL Tips, Tips 'N' Tricks and tagged , , , , , , . Bookmark the permalink. Leave a comment.

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: