Getting most used words from a column of strings in SQL
<p>So we have <a href="https://data.stackexchange.com/" rel="nofollow noreferrer">this database</a> filled with a bunch of strings, in this case post titles.</p>
<p>What I want to do is:</p>
<ol>
<li>Split the string up in to words</li>
<li>Count how many times words appear in strings</li>
<li>Give me to top 50 words</li>
<li>Not have this timeout in a data.se query</li>
</ol>
<p>I tried using the info from <a href="https://stackoverflow.com/questions/15820482/finding-most-common-words-in-a-column-using-sqlite">this SO question</a> adapted to data.se as follows:</p>
<pre><code>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) &lt;&gt; ' '
) w
group by word
order by count(*) desc
</code></pre>
<p>Unfortunately, this gives me a slew of errors:</p>
<blockquote>
<p>'substr' is not a recognized built-in function name.  Incorrect syntax
near '|'.  Incorrect syntax near 'nums'.</p>
</blockquote>
<p>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?</p>