Regexp Substring From URL

Question

I need to retrieve some word from url :

WebViewActivity - https://google.com/search/?term=iphone_5s&amp;utm_source=google&amp;utm_campaign=search_bar&amp;utm_content=search_submit

return I want :

search/iphone_5s

but I'm stuck and not really understand how to use regexp_substr to get that data.

I'm trying to use this query

regexp_substr(web_url, '\google.com/([^}]+)\/', 1,1,null,1)

which only return the 'search' word, and when I try

regexp_substr(web_url, '\google.com/([^}]+)\&', 1,1,null,1)

it turns out I get all the word until the last '&'

Wiktor Stribiżew · Accepted Answer · 2020-01-20 11:49:12Z

3

You may use a REGEXP_REPLACE to match the whole string but capture two substrings and replace with two backreferences to the capture group values:

REGEXP_REPLACE(
    'WebViewActivity - https://google.com/search/?term=iphone_5s&amp;utm_source=google&amp;utm_campaign=search_bar&amp;utm_content=search_submit',
    '.*//google\.com/([^/]+/).*[?&]term=([^&]+).*',
    '\1\2')

See the regex demo and the online Oracle demo.

Pattern details

.* - any zero or more chars other than line break chars as many as possible
//google\.com/ - a //google.com/ substring
([^/]+/) - Capturing group 1: one or more chars other than / and then a /
.* - any zero or more chars other than line break chars as many as possible
[?&]term= - ? or & and a term= substring
([^&]+) - Capturing group 2: one or more chars other than &
.* - any zero or more chars other than line break chars as many as possible

NOTE: To use this approach and get an empty result if the match is not found, append |.+ at the end of the regex pattern.

answered Jan 20, 2020 at 11:49

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

4 Comments

Benoît Zu Over a year ago

Is there a risk to catch an URL such as https://example.com/?redirect_to=https://google.com/search/?term=iphone_5s ? I'm note 100% sure due to how the URL would be encoded.

Dede Soetopo Over a year ago

thank you, it works. but what if the web url becomes google.co.uk ?

Wiktor Stribiżew Over a year ago

@BenoîtZu Sure this string will get matched, but that can be easily fixed if there is a clear set of requirements for the input string. Here is a variation of the current approach.

Wiktor Stribiżew Over a year ago

@DedeSoetopo Replace com with [^/]+, see this demo.

Collectives™ on Stack Overflow

Regexp Substring From URL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related