5

I have a table with a text column that can contain a single word, more words separated by a space or a NULL value. I need to get all the distinct words in that column (words non column entries).

For getting all the distinct column entries I use

SELECT DISTINCT(col_name) AS col_name FROM table_name ORDER BY col_name ASC

but I don't know how to get all the distinct words.

7
SELECT DISTINCT      
  SUBSTRING_INDEX(SUBSTRING_INDEX(table_name.col_name, ' ', numbers.n), ' ', -1) col_name
FROM
  (SELECT 1 n UNION ALL SELECT 2
   UNION ALL SELECT 3 UNION ALL SELECT 4) numbers INNER JOIN table_name
  ON CHAR_LENGTH(table_name.col_name)
     -CHAR_LENGTH(REPLACE(table_name.col_name, ' ', ''))>=numbers.n-1
ORDER BY
  col_name;

You can see it here: http://sqlfiddle.com/#!2/b5be5/2/0

reference: https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows

| improve this answer | |
  • Is it possible to do this on multiple columns simultaneously, to get all unique words from those columns? – Max Williams May 15 '19 at 15:49
  • And, what about limiting it words of at least four characters? – Max Williams May 15 '19 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.