Counting the number of occurrences of a substring within a string in PostgreSQL
<p>How can I count the number of occurrences of a substring within a string in PostgreSQL?</p>

<hr>

<p>Example:</p>

<p>I have a table</p>

<pre><code>CREATE TABLE test."user"
(
  uid integer NOT NULL,
  name text,
  result integer,
  CONSTRAINT pkey PRIMARY KEY (uid)
)
</code></pre>

<p>I want to write a query so that the <code>result</code> contains column how many occurrences of the substring <code>o</code> the column <code>name</code> contains. For instance, if in one row, <code>name</code> is <code>hello world</code>, the column <code>result</code> should contain <code>2</code>, since there are two <code>o</code> in the string <code>hello world</code>.</p>

<p>In other words, I'm trying to write a query that would take as input:</p>

<p><a href="https://i.stack.imgur.com/Hg3Ey.png"><img src="https://i.stack.imgur.com/Hg3Ey.png" alt="enter image description here"></a></p>

<p>and update the <code>result</code> column:</p>

<p><a href="https://i.stack.imgur.com/XO9DO.png"><img src="https://i.stack.imgur.com/XO9DO.png" alt="enter image description here"></a></p>

<hr>

<p>I am aware of the function <a href="http://www.postgresql.org/docs/current/static/functions-string.html"><code>regexp_matches</code></a> and its <code>g</code> option, which indicates that the full (<code>g</code> = global) string needs to be scanned for the presence of all occurrences of the substring). </p>

<p>Example:</p>

<pre><code>SELECT * FROM regexp_matches('hello world', 'o', 'g');
</code></pre>

<p>returns</p>

<pre><code>{o}
{o}
</code></pre>

<p>and </p>

<pre><code>SELECT COUNT(*)  FROM regexp_matches('hello world', 'o', 'g');
</code></pre>

<p>returns</p>

<pre><code>2
</code></pre>

<p>But I don't see how to write an <code>UPDATE</code> query that would update the <code>result</code> column in such a way that it would contain how many occurrences of the substring o the column <code>name</code> contains.</p>