pythonsqlregexgoogle-bigqueryqregularexpression

Replace a Regex look ahead for Google Big Query

I have some data for which i want to select the LAST VALUE BEFORE THE DELIMITER

Example- A -> B -> C -> D

In this case, i want to select "C" so i used a positive look ahead but BQ doesnt allow look aheads/behinds

Here is the Regex, (?=[^\n>]-[^\n-]$)[^\n-]*

Can someone help me replace the look ahead?

Solution

  • Consider below options (using regex and split)

    select col, 
  regexp_extract(col, r'^.*->(.*)->.*$') as extract_with_regex, 
  array_reverse(split(col, ' -> '))[offset(1)] as extract_with_split
from your_table

    if applied to sample data in your question - output is

    enter image description here