I went for a SQL Developer interview and the interviewer asked me to write code to find how many times a string appeared in a paragraph. For example, consider the below string.
    set @string = 'A stored procedures is a subroutine available to applications that access a relational database management system.     
    Such procedures are stored in the database data dictionary.     
    Uses for stored procedures include data-validation or access-control mechanisms'   

In the above example, I want to find "Procedures" repeated in a paragraph. The word repeated 3 times, and so the output should be 3. Please have a look at the below steps to see how I achieved the desired output.
 
In general, we have heard this type of question in a different manner, like how many times is a letter repeated in a given string. For example; I want to find how many times the letter "a" is repeated in the word "database".
select LEN(@string)-len(REPLACE('database','a',''))
 
After executing the above command we get the output as 3. But, it will not work in my scenario, because, my paragraph has 262 words and the "Procedures" string repeated 3 times and the length of my word is 10. If I execute the above I get the result as 30. So we need to go  a bit further to achieve the correct result. If I divide my output with the length of the word which I need to find we can get the correct result. I am attaching the sample code below, execute that code for better understanding.
    Declare @string varchar(max)    
        
    set @string = 'A stored procedures is a subroutine available to applications that access a relational database management system.     
    Such procedures are stored in the database data dictionary.     
    Uses for stored procedures include data-validation or access-control mechanisms'    
        
    select (LEN(@string)-len(REPLACE(@string,'procedures','')))/LEN('procedures')  

After executing the above code I got the output as follows.

For better understanding please try this with your own examples.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.