SQL Puzzle | Counting occurrences of a substring within a string

In this puzzle we have to count the occurrences of a substring within a string. Please check out the sample input and the expected output below-

Sample Input

DECLARE @ AS VARCHAR(1000) = ‘What the 789hell is 78 going on here dshf 78′
DECLARE @SubString VARCHAR(200) = ’78’

Expected Output

Solution 1 | Numbers Table

--

DECLARE @InputString VARCHAR(1000) =  'What the 789hell is 78 going on here dshf 78'
DECLARE @SubString VARCHAR(200) = '78'

SELECT COUNT(DISTINCT Number) Counts
FROM MASTER..SPT_VALUES N
WHERE SUBSTRING(@InputString, N.Number, LEN(@SubString)) = @SubString
GO

--

Solution 2 | String functions

--

DECLARE @ AS VARCHAR(1000) = 'What the 789hell is 78 going on here dshf 78'
DECLARE @SubString VARCHAR(200) = '78'

SELECT DATALENGTH(@) - DATALENGTH(REPLACE(@,'78','|')) Counts
GO

--

Add a comment if you have any other better solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com