CODEWITHSUNDEEP
mysqlsqlmariadb
Benvorth
Benvorth

Reputation: 7722

Count words in a database column

Let's say I have a table shortText:

ID    | SHORT_TEXT
------+---------------------------
001   | The elephants went in two by two
002   | Needles and haystack
003   | Somewhere over the rainbow
...

How to query shortText to Count the occurence of each word in column SHORT_TEXT (without using a stored procedure) to get a result like 

WORD  | OCCURENCE
------+------------
the   | 2
and   | 1
over  | 1
...

Edit:

There is no general answer (variable number of words without a given maximum) provided so far in SO.

Upvotes: 2

Views: 2164

Answers (3)

Mohsen TOA
Mohsen TOA

Reputation: 809

I've found this one interesting, count the words in specific column:

SELECT SUM(LENGTH(`YourText`) -  LENGTH(REPLACE(`YourText`, ' ', '' )) +1) FROM `table_name` WHERE `ID`='1';

Upvotes: 0

Mark Giaconia
Mark Giaconia

Reputation: 3953

I think if you build a full text index on the column, you can get the words from a table that is created to support the tokenization of strings.

this explains pretty well.... https://dev.mysql.com/doc/refman/5.6/en/innodb-ft-index-table-table.html

here's a query once you build the index....

SELECT word, doc_count, doc_id, position FROM INNODB_FT_INDEX_TABLE

OR

SELECT word,count(*) FROM INNODB_FT_INDEX_TABLE group by word;

etc.....

Note, i haven't tested this, but i've done similar in Oracle.

Upvotes: 2

Joe Love
Joe Love

Reputation: 5962

In theory, you want to divide up the string "shortText" into individual words (IE split string by spaces), then combine all the arrays into one giant list and count the words.. I'm afraid that in MySQL this may be asking too much, however, I can illustrate the principle in postgreSQL below:

 select word,count(*) occurrence
  from
 (select 
   unnest(string_to_array(lower(short_text),' ')) word 
   from shortText) words
 group by words.word
 order by count(*) desc

Upvotes: 1

Related Questions