BigQuery SQL Regex_extract repeated pattern-CodePudding

New to regexp, below is the sample query and our try as below

with string_tbl as 
( select 'https://www.this-is-abcd.com/<some_text>/ab.cd.ef.gh.ij/123456.csv' str

union all select 'https://www.this-is-pqrs.com/<some_text>/ab.abc.ef.gh.ij/123456.csv' str union all select 'https://www.this-is-pqrs.com/<some_text>/ab.abd.ef.gh.ij/123456.csv' str union all select 'https://www.this-is-abcd.com/<some_text>/ab.abc.ef.gh.ij/123456.csv' str

) select REGEXP_EXTRACT(string_tbl.str, r"ab[^/]*") from string_tbl;

output we are getting:

abcd.com
ab.abc.ef.gh.ij
ab.abd.ef.gh.ij
abcd.com

Required output:

ab.abc.ef.gh.ij
ab.abc.ef.gh.ij
ab.abd.ef.gh.ij

CodePudding user response：

Use below

select regexp_extract(string_tbl.str, r"/(ab[^/]*)") 
from string_tbl

with output