Franck Dernoncourt Franck Dernoncourt - 3 months ago 25
SQL Question

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

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:

enter image description here

and update the
result
column:

enter image description here




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

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');