Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

postgres substring regex multiple results

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?

like image 292
vw-traveller Avatar asked Oct 15 '25 21:10

vw-traveller


1 Answers

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.

like image 73
klin Avatar answered Oct 18 '25 11:10

klin