CODEWITHSUNDEEP
sqlpostgresqlconfluence
Ismar Slomic
Ismar Slomic

Reputation: 5514

Searching and returning all occurences of keyword in a Postgres text column

About database

Database table for content of an Confluence page is named bodycontent and the HTML content is stored in column named body, which is a text field. Im using Postgres database. Primary key is named bodycontentid

Result I need

For each row in the table I need to find all occurence of <image> tag where src attribute starts with "http://images.mydomain.com/allImages/%" in the body column

Example

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"/>

Result after running this query should return:

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

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

What I have tried

Im able to find all rows that has at least one occurence of the keyword Im searching for (see below), but what I need is to get list of all keywords per row that is matching my query.

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

Upvotes: 0

Views: 146

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

One method is to use regexp_split_to_table() and then some string manipulation:

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/%';

Upvotes: 1

Related Questions