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

Counts
3

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