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) )
RETURN (LEN(@pInput) -
LEN(REPLACE(@pInput, @pSearchString, ''))) /
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:
REPLACE(@pInput, @pSearchString, '')
- First step involves removing the string we are looking for from the input
string by replacing it with an empty string.
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.
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.