|
|
First Variant
One of the ways to count for the number of times a certain character occurs in a
given string is to loop through each character of the string and compare the
character, as shown in the following user-defined function.
|
CREATE FUNCTION [dbo].[ufn_CountChar] ( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURNS INT
BEGIN
DECLARE @vInputLength INT
DECLARE @vIndex INT
DECLARE @vCount INT
SET @vCount = 0
SET @vIndex = 1
SET @vInputLength = LEN(@pInput)
WHILE @vIndex <= @vInputLength
BEGIN
IF SUBSTRING(@pInput, @vIndex, 1) = @pSearchChar
SET @vCount = @vCount + 1
SET @vIndex = @vIndex + 1
END
RETURN @vCount
END
GO
Description
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 +
1).
Second Variant
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) )
RETURNS INT
BEGIN
RETURN (LEN(@pInput) - LEN(REPLACE(@pInput, @pSearchChar, '')))
END
GO
Description
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.
Third Variant
The previous version of the Count Character Occurrence user-defined function is
not case-sensitive. If we want to count the number of "t"'s in lower-case
from the same string value above, it will give us a value of 2 instead of just
a return value of 1 because it will count the first "t" even if it is in
upper-case. To make the user-defined function case-sensitive, it has to
be modified as follows:
CREATE FUNCTION [dbo].[ufn_CountChar] ( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURNS INT
BEGIN
RETURN (LEN(@pInput) - LEN(REPLACE(@pInput COLLATE SQL_Latin1_General_Cp1_CS_AS,
@pSearchChar COLLATE SQL_Latin1_General_Cp1_CS_AS, '')))
END
GO
Description
This version of the Count Character Occurrence user-defined function is the same
as the previous version but with a small modification to make it
case-sensitive. The only difference is the adding of the "COLLATE
SQL_Latin1_General_Cp1_CS_AS" clause to both the input string and the search
character. By changing the collation of both the input string and the
search character to SQL_Latin1_General_Cp1_CS_AS, which is case-sensitive and
accent-sensitive (CS_AS), the REPLACE function will only replace from the input
string the search character that matches it exactly, including the case.
|