Zahid Zahid - 4 months ago 12
SQL Question

Finding number of occurrence of characters in a string in SQL Server

Suppose the string is "this is string"

I have to define a table valued function to output the result:

+------+-------+
| char | count |
+------+-------+
| | 3 |
| t | 2 |
| h | 1 |
| i | 3 |
| s | 3 |
| a | 1 |
| r | 1 |
| n | 1 |
| g | 1 |
+------+-------+


How should I do this?

Answer
CREATE FUNCTION CountCharacterOccurences(@str nvarchar(max))
RETURNS TABLE
AS
RETURN
    WITH Split(stpos,endpos)
    AS(
        SELECT 1 AS stpos, 2 AS endpos
        UNION ALL
        SELECT endpos, endpos+1
        FROM Split
        WHERE endpos <= LEN(@str)
    )
    SELECT *, count(*) AS count
    FROM (
        SELECT 
            SUBSTRING(@str,stpos,COALESCE(NULLIF(endpos,0),LEN(@str)+1)-stpos) AS char
        FROM Split
    ) as letters
    GROUP BY char

query is a modified version of this

Usage

DECLARE @str VARCHAR(max)
SET @str = 'this is a string';

SELECT * FROM  CountCharacterOccurences(@str)

Returns

char count
    3
a   1
g   1
h   1
i   3
n   1
r   1
s   3
t   2