postgres substring regex multiple results

Question

I'm trying to find image tags urls in a text field with multiple instances.

I'm currently using this code to extract the URL from the text field:

SUBSTRING(text_field FROM 'src="([^"]*).*')

The problem is it only returns the first instance of a image tag.

Is there a way to return multiple instances of matching from a single query?

klin · Accepted Answer · 2018-04-02 23:39:02Z

6

Use the function regexp_matches() with the 'g' flag, example:

with my_table(text_field) as (
    values ('src="first";src="second"')
)

select match[1] as result
from my_table
cross join lateral regexp_matches(text_field, 'src="([^"]*)', 'g') as match

 result 
--------
 first
 second
(2 rows)

Read about POSIX Regular Expressions in the documentation.

answered Apr 2, 2018 at 23:39

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres substring regex multiple results

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related