Find the most common word in a sql column (with multiple words)

Question

For example, I have this column in sqlite3:

hello world
hello you two
hello world
hello hello

I want to extract the most popular word and its occurences. However, untill now, it seems its only possible to find the occurences of a cell. Like this:

SELECT titles, COUNT(titles) 
FROM standart_results
GROUP BY titles
ORDER BY count(*) DESC

It will return ("hello world", 2). But I want ("hello", 5).

I can not use LIKE as well since I do not know what word has the most occurences.

Do I need to transfer the data into a variable and use regex on it or can I do it with sql?

yes but for that I would have to transfer all the data into a python variable which would slow me down. If possible, I would select all the data from sqlite directly — CodingForFun, Mar 13 '21 at 11:21
Does this answer your question? [How to split comma-separated value in SQLite?](https://stackoverflow.com/questions/24258878/how-to-split-comma-separated-value-in-sqlite) — EXODIA, Mar 13 '21 at 11:22
https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=50aabb9fccb7192b6cbb8aa765d12810, just add `LIMIT 1` to only get the top result — Nick, Mar 13 '21 at 11:41

score 1 · Accepted Answer · answered Mar 13 '21 at 11:44

SQLite doesn't have really good string processing capabilities and no method for returning a table. However, it does support recursive CTEs. And you can use this construct to break the title into words:

with recursive cte as (
      select null as word, title || ' ' as rest, 0 as lev
      from t
      union all
      select substr(rest, 1, instr(rest, ' ') - 1) as word, 
             substr(rest, instr(rest, ' ') + 1) rest,
             lev + 1
      from cte
      where lev < 5 and rest like '% %'
     )
select word, count(*)
from cte
where word is not null
group by word;

Here is a db<>fiddle.

To get the top word, you can use:

select word, count(*)
from cte
where word is not null
group by word
order by count(*) desc
limit 1;

score -1 · Answer 2 · answered Mar 13 '21 at 11:39

-1

You can try this.

SELECT titles, COUNT(titles) as Appearances
FROM standart_results
GROUP BY titles
ORDER BY Appearances DESC LIMIT 1

answered Mar 13 '21 at 11:39

Masud Morshed

87
1
10

Find the most common word in a sql column (with multiple words)

2 Answers2