Find count of each character within a word / string using SQL Server 2005+

There was a question yesterday in a forum asking how to find each character count within a word. i.e., We need to count number of occurrences of each character within the given string.

For example: If the given string is 'Amala Paul' the result should be like this:

Characters, Total Occurance
A, 4
I, 2
m, 1
P, 1
u, 1

I gave a CTE Based Solution which would work on SQL Server 2005+ to achieve this. Find below the solution which I had provided.

CTE Based Solution

DECLARE @tblCountChar TABLE 
(
strString VARCHAR(20) 
)


INSERT INTO @tblCountChar VALUES('Amala Paul')


;WITH CharacterOccurrance AS
 (
   SELECT SUBSTRING(strString, 1, 1) AS Characters,
          STUFF(strString, 1, 1, '') AS ProcessedString,
          1 AS RunningNumber
   FROM @tblCountChar
   UNION ALL
   SELECT SUBSTRING(ProcessedString, 1, 1) AS Characters,
          STUFF(ProcessedString, 1, 1, '') AS ProcessedString,
          RunningNumber + 1 AS RunningNumber
   FROM CharacterOccurrance
   WHERE LEN(ProcessedString) > 0
 )
SELECT DISTINCT(Characters), COUNT(*) AS [Total Occurance]
FROM CharacterOccurrance 
-- Add this to get rid of count for spaces :). Just remove this line and check it out
WHERE LEN(Characters)=1 
Group BY Characters
GO

Alternatively instead of SUBSTRING and STUFF we can make use of RIGHT, LEFT function with a slight change to CTE to achieve the same result.