I needed an efficient and reusable way to get character counts in strings at the database level and produced the following udf which takes a string and a character and returns the number of occurrences of the character in the string.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE FUNCTION [dbo].[uf_charCount] (@string varchar(8000), @character char(1)) RETURNS SMALLINT /* Purpose: returns the number of occurrences of @character in @string Author: Kevin J. Miller (www.websolete.com) Example: SELECT dbo.uf_charCount('four score and seven years ago','s') would return: 3 */ AS BEGIN DECLARE @stringtrunc varchar(8000); SET @stringtrunc = REPLACE(@string,@character,''); -- remove the specified character RETURN (LEN(@string) - LEN(@stringtrunc)); -- return the difference in length, this is the char count END |