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:

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s