Programming, SQL, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Tips, Transact-SQL, tSQL

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: Logo

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

Facebook photo

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

Connecting to %s