Counting the number of occurrences of a substring within a string in PostgreSQL

Question

How can I count the number of occurrences of a substring within a string in PostgreSQL?

---

Example:

I have a table

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

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

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

and update the result column:

---

I am aware of the function regexp_matches and its g option, which indicates that the full (g = global) string needs to be scanned for the presence of all occurrences of the substring).

Example:

SELECT * FROM regexp_matches('hello world', 'o', 'g'); 

returns

{o} {o} 

and

SELECT COUNT(*)  FROM regexp_matches('hello world', 'o', 'g'); 

returns

2 

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

Answer 1

A common solution is based on this logic: replace the search string with an empty string and divide the difference between old and new length by the length of the search string

(CHAR_LENGTH(name) - CHAR_LENGTH(REPLACE(name, 'substring', '')))  / CHAR_LENGTH('substring') 

Hence:

UPDATE test."user" SET result =      (CHAR_LENGTH(name) - CHAR_LENGTH(REPLACE(name, 'o', '')))      / CHAR_LENGTH('o');