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
NOTE 2: 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)
|
 Updating...
Andy Hughes, Jul 17, 2012, 11:16 AM
|