Tip of the Day : SQL Server Numeric Data Types
Count Character Occurrences Function

Count Character Occurrences Function

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.