Count all occurances of different characters in a column

Question

Let's say I have two columns:

Table: MyTable

myString 
----------
 abaa
 abb
 a
 bc

I'm looking for a query that would return the count of each char within the myString column. In this case, it would return :

letter     count
-----------------
a            5
b            3
c            1

The only way I found to count chars was by a convoluted deletion method (count the length without the char, delete the char, and then recount), so I'm wondering if there would be a way to accomplish this more complicated task.

Do you have to do this in MySQL? I think it'd be easier in . — gen_Eric, Oct 23 '13 at 20:40
Good point! Can't believe I didn't think about that. Thanks. — pomegranate, Oct 23 '13 at 20:59

score 3 · Accepted Answer · edited May 23 '17 at 11:57

3

This will be slow (as will likely any solution in SQL), but you can use a numbers table to split apart the letters and count them:

SELECT
  SUBSTRING(myString, number, 1) AS Letter,
  COUNT(*) AS LetterCount
FROM
  numbers n
  JOIN myTable mt
    ON n.number <= CHAR_LENGTH(mt.myString)
GROUP BY SUBSTRING(myString, number, 1)

Working SQL Fiddle

edited May 23 '17 at 11:57

Community

1
1

answered Oct 23 '13 at 20:51

Michael Fredrickson

36,839
5
92
109

Cool way to get around this. Thanks for sharing the tidbit about the numbers table! – pomegranate Oct 23 '13 at 21:00
Don't use `LENGTH` use `CHAR_LENGTH`. – Mosty Mostacho Oct 23 '13 at 21:02

score 2 · Answer 2 · answered Oct 23 '13 at 20:42

2

You are quite close to the goal. You can achieve this with the following query :

select sum(length(mystring)) - sum(length(replace(mystring, 'a', ''))) as count_a
from mytable

answered Oct 23 '13 at 20:42

Lorenz Meyer

19,166
22
75
121

2

This will only work for the letter `'a'`... what about all other characters? – Michael Fredrickson Oct 23 '13 at 20:53
@Michael Build a query for each character you want to count ... Or build a query with a column for each character. You cannot do better. – Lorenz Meyer Oct 23 '13 at 21:02
1

@LorenzMeyer It looks like Michael Fredrickson has a good SQL only solution. – Sébastien Oct 23 '13 at 21:06

score 0 · Answer 3 · answered Oct 23 '13 at 21:54

An alternative solution is using stored procedures.
See the SQL Fiddle.

I really think this isn't the best solution, i don't recommend use this in production with a table that contains a significative length of data.

But, for study or experimental purposes.
Hugs.

Count all occurances of different characters in a column

3 Answers3

Linked