Подсчет количества вхождений подстроки внутри строки в 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, так как в строке hello world есть два o.

Другими словами, я пытаюсь написать запрос, который будет принимать в качестве входных данных:

и обновите столбец result:

Я знаю функцию regexp_matches и ее опцию g, которая указывает, что нужно сканировать полную (g= global) строку для присутствия всех вхождений подстроки).

Пример:

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

возвращает

{o}
{o}

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

возвращает

Но я не вижу, как написать запрос UPDATE, который обновил бы столбец result таким образом, чтобы он содержал количество вхождений подстроки o столбца name.

Ответ 1

Общее решение основано на этой логике: замените строку поиска пустой строкой и разделите разницу между старой и новой длиной на длину строки поиска

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

Ответ 2

A Postgres'y способ сделать это преобразует строку в массив и подсчитывает длину массива (а затем вычитает 1):

select array_length(string_to_array(name, 'o'), 1) - 1

Обратите внимание, что это работает и с более длинными подстроками.

Следовательно:

update test."user"
    set result = array_length(string_to_array(name, 'o'), 1) - 1;

Ответ 3

Другой способ:

UPDATE test."user" SET result = length(regexp_replace(name, '[^o]', '', 'g'));

Ответ 4

Occcurence_Count = LENGTH(REPLACE(string_to_search,string_to_find,'~'))-LENGTH(REPLACE(string_to_search,string_to_find,''))

Это решение немного чище, чем многие, которые я видел, особенно без делителя. Вы можете превратить это в функцию или использовать внутри выбора.
Переменные не требуются. Я использую тильду в качестве заменяющего персонажа, но любой персонаж, которого нет в наборе данных, будет работать.

Ответ 5

Вернуть количество символов,

 SELECT (LENGTH('1.1.1.1') - LENGTH(REPLACE('1.1.1.1','.',''))) AS count
--RETURN COUNT OF CHARACTER '.'