Technical Article

String Character Count

,

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.

/*____________________________________________________________________________________________________________________*//*  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
 /*____________________________________________________________________________________________________________________*/

Rate

4 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (2)

You rated this post out of 5. Change rating