tSQL to Show Key Word or Phrase Frequency

Sometimes it is useful to be able to search for the number of occurrences of a particular word or phrase within a table column. Although one can use a cursor and calculate the word count in a loop it much more efficient to do the calculations within a single statement.

Here is the Transact-SQL query that will show the number of times that the word WebControls shows up in the ErrorMessage column of an error logging table:

SELECT
(LEN(errormessage) - LEN(REPLACE(errormessage, 'webcontrols', '')) ) / LEN('webcontrols')
FROM dbo.ErrorLogs