sql - 计算PostgreSQL中字符串中子字符串出现的次数

如何计算PostgreSQL字符串中子字符串的出现次数？

例:

我有桌子

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

我想编写一个查询，以便result包含列o包含多少次出现的子字符串name。例如，如果一行中的name是hello world，则列result应该包含2，因为字符串o中有两个hello world。

换句话说，我正在尝试编写一个查询作为输入:



并更新result列:



我知道函数 regexp_matches 及其g选项，该选项指示需要扫描完整的字符串(g =全局)，以查看是否存在所有子字符串。

例:

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

退货

{o}
{o}

和

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

退货

2

但是我看不到如何编写UPDATE查询来更新result列，该查询将包含name列包含多少次子字符串。

最佳答案

常见的解决方案基于此逻辑:用空字符串替换搜索字符串，并将新旧长度之间的差除以搜索字符串的长度

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

因此:

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