Getting most used words from a column of strings in SQL

Question

So we have this database filled with a bunch of strings, in this case post titles.

What I want to do is:

1. Split the string up in to words
2. Count how many times words appear in strings
3. Give me to top 50 words
4. Not have this timeout in a data.se query

I tried using the info from this SO question adapted to data.se as follows:

select word, count(*) from (
select (case when instr(substr(p.Title, nums.n+1), ' ') then substr(p.Title, nums.n+1)
             else substr(p.Title, nums.n+1, instr(substr(p.Title, nums.n+1), ' ') - 1)
        end) as word
from (select ' '||Title as string
      from Posts p
     )Posts cross join
     (select 1 as n union all select 2 union all select 10
     ) nums
where substr(p.Title, nums.n, 1) = ' ' and substr(p.Title, nums.n, 1) <> ' '
) w
group by word
order by count(*) desc

Unfortunately, this gives me a slew of errors:

'substr' is not a recognized built-in function name. Incorrect syntax near '|'. Incorrect syntax near 'nums'.

So given a column of strings in SQL with a variable amount of text in each string, how can I get a list of the most frequently used X words?

Answer 1

Query solution (No Split Function Required)

PostgreSQL

select word, count(*) from 
(
    -- get 1st words
    select split_part(title, ' ', 1) as word
    from posts

    union all

    -- get 2nd words
    select split_part(title, ' ', 2) as word
    from posts

    union all

    -- get 3rd words
    select split_part(title, ' ', 3) as word
    from posts

    -- can do this as many times as the number of words in longest title

) words
where word is not null
and word NOT IN ('', 'and', 'for', 'of', 'on')
group by word
order by count desc
limit 50;

for a concise version, see: https://dba.stackexchange.com/a/82456/95929