How Many Times a Substring Occurs in a Value? MS SQL

There are situations where you may need to determine the number of occurrences of a specific substring within a larger string. Whether it’s analyzing textual data or performing data cleansing tasks, counting substring occurrences can be a valuable operation.

Here is one of the popular solutions to calculate how many times a substring occurs in the string:

DECLARE @mySuperString NVARCHAR(100) = 'Yarkul is a blog about programming. Yarkul, hello world! yarkul'
DECLARE @myWord NVARCHAR(10) = 'yarkul'
SELECT (LEN(@mySuperString) - LEN(REPLACE (@mySuperString, @myWord, ''))) / LEN(@myWord)

This method calculates the difference in length between the original string and the string with the substring removed, then divides it by the length of the substring to obtain the occurrence count.

Execution example:
How Many Times a Substring Occurs in a Value MS SQL
Here you can see that ‘yarkul’ word occurs 3 times. Take into account that it’s a case insensitive calculation.

Leave a Comment