Get Count of Number of Occurrences of Character in String

DROP FUNCTION dbo.udf_GetCountOfCharacterInString

GO

CREATE FUNCTION dbo.udf_GetCountOfCharacterInString

(

@Character CHAR(1), -- the character to search for

@String VARCHAR(MAX), -- the string to search

@IsCaseSensitive BIT = 0 -- is the search honor case sensitive?

)

RETURNS INT

AS

/**********************************************************

Purpose: Returns a count of the number of times the supplied

character occurs at in the supplied string.

NOTE: The case sensitive flag will only cause case sensitivity (CS) to

be applied in a non-CS DB. If will not apply non-CS in a CS DB

Utilises udf_NumbersTable to provide the required 'tally' table used

when parsing the string for matches

Author: ChillyDBA

History 17 Jul 2012 - Initial Issue

***********************************************************/

BEGIN

DECLARE @CountofOccurences INT

IF @IsCaseSensitive = 0

BEGIN

SELECT @CountofOccurences = MAX(RowNumber)

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY i ASC) AS RowNumber

FROM dbo.udf_NumbersTable(1, DATALENGTH(@String), 1)

WHERE i <= LEN(@String)

AND SUBSTRING(@String,i,1) = @Character

) AS x

END

ELSE

BEGIN

SELECT @CountofOccurences = MAX(RowNumber)

FROM

(

SELECT ROW_NUMBER() OVER (ORDER BY i ASC) AS RowNumber

FROM dbo.udf_NumbersTable(1, DATALENGTH(@String), 1)

WHERE i <= LEN(@String)

AND ASCII(SUBSTRING(@String,i,1)) = ASCII(@Character)

) AS x

END

RETURN @CountofOccurences

END