MySQL – How to Get All Distinct Words in a Column

MySQL

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.

Best Answer

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

Related Solutions

Mysql – Getting unique names when the IDs are different (DISTINCT ?)

Here is the query

SELECT id,name FROM
(SELECT name,MAX(id) id FROM `mytable` GROUP BY name) A
ORDER BY id DESC LIMIT 2;

Here is some sample data

mysql> use test
Database changed
mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.03 sec)

mysql> create table mytable
    -> (id int not null auto_increment,
    -> name varchar(20),primary key (id));
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO mytable (name) VALUES
    -> ('John'),('John'),('Peter'),('Mike'),('Mike');
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable;
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | John  |
|  3 | Peter |
|  4 | Mike  |
|  5 | Mike  |
+----+-------+
5 rows in set (0.00 sec)

mysql> SELECT id,name FROM
    -> (SELECT name,MAX(id) id FROM `mytable` GROUP BY name) A
    -> ORDER BY id DESC LIMIT 2;
+------+-------+
| id   | name  |
+------+-------+
|    5 | Mike  |
|    3 | Peter |
+------+-------+
2 rows in set (0.00 sec)

mysql>

Mysql – incorrect result when filtering by association

You need one more join (a LEFT join with IS NULL check) or a NOT EXISTS subquery and to move the condition (reversed!) there:

SELECT * FROM dockets
INNER JOIN entries 
ON entries.docket_id = docket.id 
WHERE NOT EXISTS
      ( SELECT 1
        FROM entries AS e
        WHERE e.docket_id = docket.id 
          AND e.docket_type LIKE '%PENDING%'
      ) ;

Best Answer

Related Solutions

Mysql – Getting unique names when the IDs are different (DISTINCT ?)

Mysql – incorrect result when filtering by association

Related Question