A while ago I once attended an interview where I was asked to write a short script that counts each letter of a given string and provide a grouping for each. This Script counts the number of times each letter appears in a string. It gives each letter and the number of occurrences. I have provided two methods. The first is a CTE based method and the second is a while loop. I havent tested this with a large data set but for a string with few characters both methods will be good performance wise.
String Character Count
/*____________________________________________________________________________________________________________________*//* First Approach: CTE*/DECLARE @SplitList NVARCHAR(4000);
SELECT @SplitList = 'Count Howmany Characters InString';
WITH Listings (Position, CharacterSymbol) AS
(
SELECT 1, SUBSTRING(@SplitList, 1, 1)
UNION ALL
SELECT s.Position + 1, SUBSTRING(@SplitList,s.Position + 1, 1)
FROM Listings AS s
WHERE s.Position <= LEN(@SplitList) - 1
)
SELECT CASE WHEN s.CharacterSymbol = '' THEN 'Empty Space' ELSE s.CharacterSymbol END AS WCharacter , COUNT(s.CharacterSymbol)
FROM Listings AS s
GROUP BY s.CharacterSymbol
/*____________________________________________________________________________________________________________________*//* Loop */DECLARE @string VARCHAR(4000)
DECLARE @wcount INT
DECLARE @Counter INT
DECLARE @len INT
DECLARE @char VARCHAR(2)
set @string = 'Count Howmany Characters InString'
SET @wcount = 0
SET @Counter = 1
SET @len = LEN(@string)
DECLARE @Table TABLE(ID INT, WCharacter VARCHAR(2))
WHILE @Counter <= @len
BEGIN
SET @char = SUBSTRING(@string, @Counter, 1)
INSERT @Table(ID, WCharacter )
SELECT @wcount,@char
SET @Counter= @Counter+ 1
END
SELECT CASE WHEN WCharacter = '' THEN 'Empty Space' ELSE WCharacter END AS WCharacter , COUNT(WCharacter) AS TotalOfEach
FROM @Table
GROUP BY WCharacter
/*____________________________________________________________________________________________________________________*/