Tip of the Day : SQL Server Numeric Data Types
Count String Occurrence Function

Count String Occurrence Function

Here's a user-defined function that returns the number of times a given string exists on another string.  The first parameter is the string to be searched while the second parameter is the string to be searched for.

CREATE FUNCTION [dbo].[ufn_CountString]
( @pInput VARCHAR(8000), @pSearchString VARCHAR(100) )
RETURNS INT
BEGIN

    RETURN (LEN(@pInput) - 
            LEN(REPLACE(@pInput, @pSearchString, ''))) /
            LEN(@pSearchString)

END
GO

Description

Counting the number of times a string exists in another string is quite easy, as can be seen from the simple code of the user-defined function.  The process can be summarized into the following steps:

  1. REPLACE(@pInput, @pSearchString, '') - First step involves removing the string we are looking for from the input string by replacing it with an empty string.
  2. LEN(@pInput) - LEN(<ReplacedString>) - After replacing the search string with an empty string, we now get the difference between the length of the original string and the new string with the replaced value.
  3. <LengthDifference> / LEN(@pSearchString) - The final step, which becomes the return value of the user-defined function, is to divide the result of the previous step with the length of the search string.  This will give use the number of times the search string exists within the other string.

To illustrate further, let's say we want to count the number of times the word "business" from the following definition of "Business Intelligence":

"Business Intelligence, or simply BI, is a process of gathering, storing, processing, analyzing and disseminating decision-making information to business leaders to help them make better business decision."

The first step in the user-defined function is to replace the search string from the second string.  So replacing the string "business" from the previous text will give use the following result:

" Intelligence, or simply BI, is a process of gathering, storing, processing, analyzing and disseminating decision-making information to  leaders to help them make better  decision."

The next step is subtracting the length of the resulting text after the replace from the length of the original text.  The original text has a length of 204 while the resulting text has a length of 180, giving us a difference of 24.

The last step determines the number of times the search string occurred in the other string.  This is accomplished by dividing the difference between the length original text and length of the resulting text, which is 24, by the length of the string being searched for, which is 8, giving the result of 3, the number of times the word "business" occurred in the long text above.