CODEWITHSUNDEEP
sql-server
Osprey
Osprey

Reputation: 1545

Counting the occurances of each letter in a string in SQL

I have a phrase such as "BROWNSEA ISLAND". Is there a way in MS SQL to derive a table that shows the number of times a letter occurs? For example:

B   1
R   1
O   1
W   1
N   2
S   2
E   1
etc....

Upvotes: 1

Views: 1080

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

On most modern versions of SQL Server, 

DECLARE @s varchar(255) = 'BROWNSEA ISLAND';

;WITH n(n) AS
(
  SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n < LEN(@s)
)
SELECT 
  Letter = SUBSTRING(@s,n,1), 
  Occurs = COUNT(*) OVER (PARTITION BY SUBSTRING(@s,n,1))
FROM n
ORDER BY n;

This will count spaces, numbers, and non-alphanumeric characters in addition to letters. If you want to leave the count NULL for those, then you can say:

DECLARE @s varchar(255) = 'BROWNSEA ISLAND';

;WITH n(n) AS
(
  SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n < LEN(@s)
),
l AS
(
  SELECT n, Letter = SUBSTRING(@s,n,1) FROM n
)
SELECT Letter, Occurs = CASE 
     WHEN ASCII(Letter) BETWEEN 65 AND 90
      OR ASCII(Letter) BETWEEN 97 AND 122 
     THEN COUNT(*) OVER (PARTITION BY Letter) 
  END
FROM l ORDER BY n;

If you want to leave those out, you can move the CASE expression to the WHERE clause. If you want upper-case Z and lower-case z to count differently, let us know.

If you want to use this in a function so that you can apply it to multiple values across multiple rows, instead of a single variable:

CREATE FUNCTION dbo.CountLetters(@s nvarchar(255))
RETURNS TABLE WITH SCHEMABINDING
AS
   RETURN 
   (
     WITH n(n) AS
     (
       SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n < LEN(@s)
     )
     SELECT [Rank] = n, 
       Letter = SUBSTRING(@s,n,1), 
       Occurs = COUNT(*) OVER (PARTITION BY SUBSTRING(@s,n,1))
     FROM n
   );
GO

Sample usage:

SELECT o.name, f.[Rank], f.Letter, f.Occurs
  FROM sys.objects AS o
  CROSS APPLY dbo.CountLetters(name) AS f
  ORDER BY o.name, f.[Rank];

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

You could create a calendar table of all letters the alphabet, and then do a join:

WITH letters AS (
    SELECT 'A' AS letter UNION ALL
    SELECT 'B' UNION ALL
    SELECT 'C' UNION ALL
    ...
    SELECT 'Z'
)

SELECT
    letter,
    LEN('BROWNSEA ISLAND') - LEN(REPLACE('BROWNSEA ISLAND', letter, '')) AS count
FROM letters
ORDER BY
    letter;

Demo

Upvotes: 1

Related Questions