Поиск и возврат всех вхождений ключевого слова в текстовом столбце Postgres

О базе данных

Таблица базы данных для содержимого страницы Confluence называется bodycontent, а содержимое HTML хранится в столбце с именем body, который является текстовым полем. Я использую базу данных Postgres. Первичный ключ называется bodycontentid

Результат, который мне нужен

Для каждой строки в таблице мне нужно найти все вхождения тега <image>, где атрибут src начинается с "http://images.mydomain.com/allImages/%" в столбце body

Пример

Let say that body with bodycontentid = 12345 contains following text: 

<h1>Chapter 1</h1>
<image src="http://www.google.com/image/111.jpg"/>
<h1>Chapter 2</h1>
<image src="http://images.mydomain.com/allImages/222.jpg"/>
<h1>Chapter 3</h1>
<image src="http://images.mydomain.com/allImages/333.jpg"/>

Результат после выполнения этого запроса должен возвращаться:

bodycontentid: 12345 body: http://images.mydomain.com/allImages/222.jpg

bodycontentid: 12345 body: http://images.mydomain.com/allImages/333.jpg

Что я пробовал

Я могу найти все строки, в которых есть хотя бы одно вхождение ключевого слова, которое я ищу (см. Ниже), но мне нужно получить список всех ключевых слов в каждой строке, которые соответствуют моему запросу.

SELECT *
FROM bodycontent
WHERE body LIKE '%http://images.mydomain.com/allImages/%'

postgresql sql confluence
person Ismar Slomic    schedule 12.03.2016    source источник
comment
функция regexp_matches, regexp_matches(body, '<image src="(http://images.mydomain.com/allImages/[^"]*)', 'g') в вашем случае.   -  person Abelisto    schedule 12.03.2016

Ответы (1)


arrow_upward
1
arrow_downward

Один из способов — использовать regexp_split_to_table(), а затем некоторые манипуляции со строками:

select bc.bodycontentid,
       left(rst.s, position('"' in rst.s) - 1) as domain
from bodycontent bc, lateral
     regexp_split_to_table(bc.body, E'srce="') rst(s)
where rst.s like 'http://images.mydomain.com/allImages/%';
person Gordon Linoff    schedule 12.03.2016
comment
Превосходно! Работает точно так, как ожидалось! Большое спасибо за быстрый ответ! - person Ismar Slomic; 12.03.2016