Using regex to extract product name from URL with Google BigQuery

Question

I am trying to extract the product names from the URLs below using regex in Google BigQuery:

https://www.example.com/en/uk/product/clothing/trousers-leggings/cool-grey-joggers-9800977

Product name should be: cool-grey-joggers

https://www.example.com/en/uk/product/hot-denim-dress-7842666

Product name should be: hot-denim-dress

I have tried the following regular expression:

'/product/(.+)-[0-9]+$'

but this seems to be greedy matching and will return:

clothing/trousers-leggings/cool-grey-joggers

hot-denim-dress

I have tried numerous variations of the above but all are either not matching anything or capturing too much.

How can I modify so it captures the product name only and not the the parent sub folders as well?

The fourth bird · Accepted Answer · 2021-02-18 09:55:16Z

3

If /product/ has to be part of the path:

/product/(?:[^/]+/)*([^/]+)-[0-9]+$

The pattern matches:

/product/ Match literally
(?:[^/]+/)* Optionally repeat matching parts that end with a /
([^/]+) Capture group 1, match 1+ occurrence of any char except /
-[0-9]+$ Match - and 1+ digits till the end of the string

Regex demo

answered Feb 18, 2021 at 9:55

The fourth bird

165k16 gold badges61 silver badges75 bronze badges

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

Comments

Sergey Geron · Accepted Answer · 2021-02-18 06:17:14Z

1

Try this one:

SELECT REGEXP_EXTRACT('https://www.example.com/en/uk/product/clothing/trousers-leggings/cool-grey-joggers-9800977', r'([^/]+)-[0-9]+$')
UNION ALL
SELECT REGEXP_EXTRACT('https://www.example.com/en/uk/product/hot-denim-dress-7842666', r'([^/]+)-[0-9]+$')

answered Feb 18, 2021 at 6:17

Sergey Geron

10.5k4 gold badges28 silver badges38 bronze badges

Collectives™ on Stack Overflow

Using regex to extract product name from URL with Google BigQuery

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related