Context
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.