Подсчет количества вхождений подстроки внутри строки в PostgreSQL

22

Как подсчитать количество вхождений подстроки внутри строки в 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 = global) строка должна быть проверена на наличие всех вхождений подстроки).

Пример:

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

возвращает

{o}
{o}

и

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

возвращает

2

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

    
задан Franck Dernoncourt 02.04.2016 в 19:18
источник
  • Возможный дубликат столбца PostgreSQL количества раз, когда подстрока встречается в тексте –  Evan Carroll 10.03.2017 в 02:15

4 ответа

24

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

(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');
    
ответ дан dnoeth 02.04.2016 в 19:28
  • Это солидный ответ, и все правильно. Вы можете быть заинтересованы в моем написании всех методов этого –  Evan Carroll 10.03.2017 в 02:05
  • Спасибо! Кто-нибудь знает, почему нет более простого способа? Я имею в виду, что REPLACE уже справляется с проблемой сканирования всей строки для всех вхождений, почему бы не сделать что-то, что делает половину работы REPLACE - просто подсчитайте появления –  Aleksandr Levchuk 19.03.2017 в 00:27
  • @AleksandrLevchuk: Ну, вы можете написать свою собственную пользовательскую функцию, выполняющую этот расчет, например. есть REGEXP_COUNT Oracle в файле enterpriseisedb.com/docs/en/9.5/eeguide/.... –  dnoeth 19.03.2017 в 12:20
16

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;
    
ответ дан Gordon Linoff 02.04.2016 в 19:31
  • Если кому-то нужно regexp, это решение с "regexp_split_to_array" вместо "string_to_array" тоже работает. –  Le Droid 26.10.2016 в 18:19
  • Это решение существенно медленнее, чем предложение @ dnoeth. Я не думаю, что это больше - Postgres-y. Когда все происходит быстрее и более переносимо в другом методе, я думаю, что мы это лучше называем. знак равно –  Evan Carroll 10.03.2017 в 02:53
  • @EvanCarroll К сожалению, ответ dnoeth не будет работать для совпадений регулярных выражений, так как вы можете не знать длину совпадения. Этот ответ будет работать как для совпадений регулярных выражений, так и для совпадающих строк. Я думаю, что мы лучше называем решение, которое работает на все, что вы пытаетесь сделать :) –  WebWanderer 20.04.2017 в 17:20
0

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

UPDATE test."user" SET result = length(regexp_replace(name, '[^o]', '', 'g'));
    
ответ дан bnson 16.08.2016 в 11:18
0

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

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

    
ответ дан Robert Bondy 03.04.2018 в 15:37