Get Occurrences of Character in String

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 17 Jul 2012

Description

Returns the ordinal position of all occurrences of the supplied character in the supplied string

Takes 3 parameters:

    • @Character - the character to be searched for

    • @String - the string to be searched

    • @IsCaseSensitive - 1 indicates that case sensitivity should be enforced during the search. Default 0.

NOTE1: For case sensitivity (CS), this will enforce CS in a non-CS database but will not relax CS in a CS database

NOTE2: The function utilises the function udf_NumbersTable. This has several versions, depending on SQL Server version.

Code

Function:

DROP FUNCTION dbo.[udf_GetOccurrencesOfCharacterInString]

GO

CREATE FUNCTION [dbo].[udf_GetOccurrencesOfCharacterInString]

(

@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 @Results TABLE

(

Position INT,

CHARacter CHAR(1) -- this is only returned as a confidence check. Not really necessary

)

AS

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

Purpose: Returns a list of character positions that 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

IF @IsCaseSensitive = 0

BEGIN

INSERT @Results (Position, CHARacter)

SELECT i, SUBSTRING(@String,i,1)

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

WHERE i <= LEN(@String)

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

ORDER BY i

END

ELSE

BEGIN

INSERT @Results (Position, CHARacter)

SELECT i, SUBSTRING(@String,i,1)

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

WHERE i <= LEN(@String)

AND ASCII(SUBSTRING(@String,i,1)) = ASCII(@Character) -- by converting the character to ASCII value, case-sensitivity is enforced

ORDER BY i

END

RETURN

END

Test Code:

SELECT * FROM dbo.udf_GetOccurrencesOfCharacterInString('e', ',Element01,Element02,Element03,Element04,Element05,', 0)

SELECT * FROM dbo.udf_GetOccurrencesOfCharacterInString('e', ',Element01,Element02,Element03,Element04,Element05,', 1)