How To Find A String Repeated In Paragraph By Using SQL Server


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.
  1. set @string = 'A stored procedures is a subroutine available to applications that access a relational database management system.     
  2. Such procedures are stored in the database data dictionary.     
  3. 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.
  1. Declare @string varchar(max)    
  3. set @string = 'A stored procedures is a subroutine available to applications that access a relational database management system.     
  4. Such procedures are stored in the database data dictionary.     
  5. Uses for stored procedures include data-validation or access-control mechanisms'    
  7. select (LEN(@string)-len(REPLACE(@string,'procedures','')))/LEN('procedures')  
After executing the above code I got the output as follows.
How To Find a String repeated in Paragraph by Using Sql Server
For better understanding please try this with your own examples.