在PostgreSQL中,如何计算字符串中子字符串的出现次数?


例子:

我有一张桌子

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

我想写一个查询,以便result列包含name列包含的子字符串o的出现次数.例如,如果在一行中,namehello world,那么列result应该包含2,因为字符串hello world中有两个o.

换句话说,我正在try 编写一个将作为输入的查询:

enter image description here

并更新result栏:

enter image description here


我知道函数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列包含的子字符串的出现次数.

推荐答案

一个常见的解决方案基于这个逻辑: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');

Sql相关问答推荐

使用SQL旋转表的列(Snowflake)

基于前面行的值:当x&>2时重复1,当连续3行x=0时则重复0

在多个柱上连接时,如何确定连接条件?

使用Lead获取下一个不同的日期

为什么在postgres中,横向连接比相关子查询快?

Oracle SQL根据列中的条件 Select 最大记录数

NULL-生成的列中连接的字符串的输入

如何将我的联接数据放入每个用户每月多行的列中?

按分类标准检索记录

存储过程太慢

对于小数据集,EF / SQL 语句花费的时间太长

没有调用子查询的嵌套 JOIN语法是什么?

SQL 如何根据当前事件和下一个事件确定操作的持续时间?

SQL 中的第一个值和倒数第二个值

SQL Server: 将JSON对象数组转换为表格格式

我如何编写一个遍历数组数组并将所有值连接成一个字符串的 postgres 函数

Postgres 条件求和函数

sql count distinct by column 和 sum false 和 true

有条件的 Postgresql LAG

如何将表 A 中的 FK 替换为表 B 中的相应值?