MySQL query to get all the distinct letters used across the rows

Question

I have the table "words":

id
name

insert into words values(null, 'one');
insert into words values(null, 'two');
insert into words values(null, 'three');

I need to find out, distinctly, the letters used across all over the table. In this case the result would be:

o
n
e
t
w
h
r

I actually dont really have a clue on how to do this. I found some stored procedure that seems to solve it. Some kind of iteration through all the rows where new letters are added to the final results. Is there a way to do this with a pure sql-command?

Aren`t procedures pure SQL commands? Are you working with PHP as well or only with MySQL/SQL/etc? Take a look at [this](http://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql) — gabriel garcia, Feb 27 '16 at 10:09
I could do this with PHP. Im using laravel and database seed. I can solve it in some manner. But it would be nice of it could be done with a single sql query. — oderfla, Feb 27 '16 at 10:12
You'll have to create a function to do this.Probably use SUBSTRING(colname,1,1) for each character to get them individually. — Golden_flash, Feb 27 '16 at 10:18
Questions like this crop up once in a while - usually in relation to some kind of genetic research algorithm. The usual approach is to spit the dataset out to PHP, say, and handle the processing there. — Strawberry, Feb 27 '16 at 10:31
Handing the part to PHP would be easier but having a mysql function that can perform the same will be much faster. — Golden_flash, Feb 27 '16 at 10:35

score 2 · Accepted Answer · answered Feb 27 '16 at 12:01

You could use SUBSTRING with derived tally table:

SELECT DISTINCT SUBSTRING(word, n, 1) AS letter
FROM words w
CROSS JOIN 
(
   SELECT a.N + b.N * 10 + c.N * 1000 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
) n
WHERE n <= LENGTH(w.word)
ORDER BY letter;

SqlFiddleDemo

If you need o and O as different letters add COLLATE utf8_bin:

SELECT DISTINCT SUBSTRING(word, n, 1) COLLATE utf8_bin AS letter

MySQL query to get all the distinct letters used across the rows

1 Answers1