计算PostgreSQL中字符串中子字符串出现的次数 [英] Counting the number of occurrences of a substring within a string in PostgreSQL

问题描述

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

How can I count the number of occurrences of a substring within a string in 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 ，因为字符串 hello中有两个 o 世界。

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:

并更新结果列：

我知道该功能 regexp_matches 及其 g 选项，该选项指示需要扫描完整的字符串（ g = global），以查看是否存在所有出现的亚蛋白G）。

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).

示例：

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

返回

{o}
{o}

和

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

返回

2

但是我不知道如何写 UPDATE 查询，该查询将更新 result 列，其方式为包含名称包含。

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.

推荐答案

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

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

因此：

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

这篇关于计算PostgreSQL中字符串中子字符串出现的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！