Search code examples
regexlooker-studiore2

extracting string from URL using regex (on data studio)

I have the following landing page

city7900/
cityid=7900
city7900-t40094705.nb1/

and I want to merge everything to

7900

on data studio

I tried using

REGEXP_EXTRACT(Landing Page,'city([^&]+))

and it only extract the

city7900/
cityid=7900

ones and tried

REGEXP_EXTRACT(Landing Page,'city([^&]+)|city([^&]+)(.*?)\\-')

and it only extracts the city7900-t40094705.nb1/

how can I extract all of them?

Solution

  • You can use

    REGEXP_EXTRACT(Landing Page,'city[^0-9]*([0-9]+)')

    See the regex demo. Details:

    • city - a string
    • [^0-9]* - zero or more chars other than digits
    • ([0-9]+) - Capturing group 1: one or more digits.