Regexp_Extract - Data Studio extract value after second underscore-CodePudding

I have a simple string separated by underscores from which I need to pull all the values after a specific underscore using a regular expression with the REGEXP_EXTRACT formula in Google Data Studio

The strings look like this:

ABC123_DEF456_GHI789-JKL274

Basically the values after the second underscore can be alphanumeric or symbols as well.

I need to pull the values after the second underscore. In the case of the example I gave, it would be:

GHI789-JKL274

Any ideas would be greatly appreciated.

Of course, many thanks in advance.

CodePudding user response：

You need to use

REGEXP_EXTRACT(some_field, "^(?:[^_]*_){2}([^_]*)")

See the regex demo.

Details:

^ - start of string
(?:[^_]*_){2} - two occurrences of any zero or more chars other than _ and then a _
([^_]*) - Capturing group #1: zero or more chars other than _.

CodePudding user response：

With your shown samples please try following regex.

^(?:.*?_){2}([^_]*)

REGEXP_EXTRACT(yourField, "^(?:.*?_){2}([^_]*)")

Here is the Online Demo for used regex.

Explanation: Adding a detailed explanation for used regex here.

^      ##Matching from starting of the value here.
(?:    ##Opening 1 non-capturing group here.
  .*?_ ##Using Lazy match to match till next occurrence of _ here.
){2}   ##Closing non-capturing group here and matching its 2 occurrences.
(      ##Creating 1 and only capturing group here.
 [^_]* ##Matching everything before _ here.
)      ##Closing capturing group here.