title: Counting the number of occurrences of a substring within a string in PostgreSQL
body: <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>