As can be seen from the user-defined function, it loops through each character
in the input string (WHILE @vIndex <=
@vInputLength) and compares the string characer against the input
search characer (IF SUBSTRING(@pInput,
@vIndex, 1) = @pSearchChar). If they are the same, the counter
is incremented by 1 (SET @vCount = @vCount +
Here's another way of determining the number of times a certain character occur
in a given string without the use of a loop.
CREATE FUNCTION [dbo].[ufn_CountChar] ( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURN (LEN(@pInput) - LEN(REPLACE(@pInput, @pSearchChar, '')))
This method is more efficient than the previous version primarily because it
doesn't use a loop to determine the number of times the search character occurs
in the given string. What it does is replace from the input string the
search character with an empty character. It then subtracts the length of
the resulting string from the length of the original string to determine the
number of times the search character occurs in the input string.
To further illustrate the method performed by the user-defined function, let's
say you have a string value of "The quick brown fox jumps over the lazy dog."
and you want to determine the number of times the letter "o" occur in the
string. The first step it does is to replace the search character, in
this case the letter "o", with an empty character/string. So the
resulting string after the REPLACE will be "The quick brwn fx jumps ver the
lazy dg." Now we subtract the length of this resulting string, which is
40, from the length of the original input string, which is 44, and that gives
us 4, which is the number of "o"'s in the given string value.