How to find how many different words there are in a column in sql?

Question

If I have a table like so:

How do I view how many different words there are? In this example it would be three:

If I understood it weel, you want an SQL to count the distinct fruits. — Ed de Almeida, Apr 06 '16 at 00:11
Can you pls show me an example as to how select distinct would be used? @GordonLinoff — Ideen, Apr 06 '16 at 00:12
Then all you need is a SELECT DISTINCT. See this: http://www.tutorialspoint.com/sql/sql-distinct-keyword.htm — Ed de Almeida, Apr 06 '16 at 00:13
Possible duplicate of [SQL to find the number of distinct values in a column](http://stackoverflow.com/questions/141562/sql-to-find-the-number-of-distinct-values-in-a-column) — Severino Lorilla Jr., Apr 06 '16 at 00:16

score 3 · Accepted Answer · answered Apr 06 '16 at 00:15

3

SELECT COUNT(DISTINCT column_name) AS some_alias FROM table_name

answered Apr 06 '16 at 00:15

If I had "Apples, Oranges" in one row. Would it be possible to split the fruits into two between the commas? – Ideen Apr 06 '16 at 00:38
well, you can't do that with the query above as it will look for the whole string "Apples, Oranges". Maybe you can do some pre-processing before running the query? – Severino Lorilla Jr. Apr 06 '16 at 00:47

score 1 · Answer 2 · answered Apr 06 '16 at 03:48

To get the no. of diff words you can use either group by or distinct. select words_column_name, count(words_column_name) as no_of_repetation from table_name group by words_column_name. or just select distinct(words_column_name) from table_name

2.To split the word, there is no in build function, but you can do it using substring() & locate(). like:

SUBSTRING_INDEX(SUBSTRING_INDEX(fullword, ',', 1), ',', -1) AS first_fruit,
    TRIM( SUBSTR(fullname, LOCATE(',', fullword)) ) AS last_fruit

But this is applicable only for 2 fruit combination, for more you need to write some php script or mysql function

2 Answers2