Split a column at every whitespace Let's say I got word transcriptions (space separated) in one column. Furthermore I got a set of legal phones. 

How can I split every transcription @ a whitespace to check whether all used  phones are legal ones?

Main question is: how to get a distinct on a column split @ a whitespace?

Let's say the data looks like this:

ID | transcriptions
 1 | th i s
 2 | i s 
 3 | s o m e
 4 | t e st
 5 | d a t a


Now the output should look like the following:


  th
  i
  s
  o
  m
  e
  t
  st
  d
  a
This should work (sqlfiddle):

SELECT distinct SUBSTRING_INDEX( SUBSTRING_INDEX( transcriptions, ' ', l10.n * 10 + l1.n + 1), ' ', -1) AS transcription
FROM data
CROSS JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UN ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 0
) l1
CROSS JOIN (
    SELECT 1 AS n 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 UNION ALL SELECT 0
) l10
WHERE LENGTH(transcriptions) - LENGTH( REPLACE( transcriptions, ' ', '')) &gt;= l10.n * 10 + l1.n
;


Please note that it may not be extremely efficient. You may consider changing your data model and avoid spliting string using SQL.

Each CROSS JOIN (l1 and l10) create a number table from 1 to 100 (10*10). If strings are longer that 100, extra CROSS JOIN must be added (10*10*10 => 1.000, 10*10*10*10 => 10.000, ...).
The number table is used on characters at each position in the number table and search for spaces.

Sample Data:

CREATE TABLE data(id int, transcriptions varchar(100));
INSERT INTO data(id, transcriptions) VALUES
    (1, 'th i s')
    , (2, 'i s')
    , (3, 's o m e')
    , (4, 't e st')
    , (5, 'd a t a');


Output (no specific order):

transcription
i
s
o
e
a
m
st
t
th
d