Search code examples
regexlooker-studiore2

Data Studio Regex (Google RE2) to Extract Subdirectory from Path

I'm working with a Google Data Studio field that has a page URL Path contained within it. Examples:

  • /
  • /sample-url
  • /sample-url-2/
  • /#sample-url-5/
  • /sample-url-3/sample-url-4
  • /sample-url-3/sample-url-6

In each one, I want to be capturing the bold portion in a custom formula/field--from the first slash, up to but excluding the second slash if there is one, and also including the first slash if that's the whole path. (In essence, the first subdirectory.) I would be open to recording the second backslash when there is one if that would make the solution simpler, but I'm guessing it's more complicated that way. I tried the following:

REGEXP_EXTRACT(Field, "^/[^/]+/$")

But it didn't work; everything returned null. What is wrong with that string?

Solution

  • The ^/[^/]+/$ pattern matches a string that starts with a / char, then contains one or more chars other than / and then ends with a / char. So, you can only match strings like /abc/, /123abc/, /abc-1 2 3.?!/, etc.

    You can use

    REGEXP_EXTRACT(Field, "^(/[^/]*)")

    See the regex demo.

    NOTE: REGEXP_EXTRACT requires a capturing group in the pattern, the content captured is the return value.

    Here, ^ matches the start of string and (/[^/]*) is a capturing group with ID 1 that matches a / char and then any zero or more chars other than / (with [^/]*).