Regexp_Extract nth position in delimited string

Question

I have a string in the format:

abc_fjs_dja_sja_dj_sadjasdksa_sdjakd_match_fsja_fsdk

I want to REGEXP_EXTRACT the string in the 8th delimited position (_ as the delimiter).

How would I do this using the Regex syntax used in DataStudio?

I have tried this: (?:[^_]*_){8}(.*?)

But this matches anything up to and including the 8th delimited segment of the string, whaeras I want JUST the 8th segment.

Thanks

Answer 1

You may unwrap the pattern a bit and capture 0 or more chars other than _ after the first seven _ delimited parts with ([^_]*):

^(?:[^_]*_){7}([^_]*)

See the regex demo

Details

• ^ - start of string
• (?:[^_]*_){7} - 7 repetitions of
  • [^_]* - 0 or more chars other than _
  • _ - an underscore
• ([^_]*) - Capturing group 1: 0 or more chars other than _

REGEXP_EXTRACT returns the contents of the capturing group.