Cool Tips‎ > ‎String Manipulation‎ > ‎

String Manipulation - Get Occurences 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)


ċ
udf_GetOccurrencesOfCharacterInString.sql
(2k)
Andy Hughes,
Jul 17, 2012, 11:16 AM
Comments