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?
CodePudding user response:
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
CodePudding user response:
You don't need any lookarounds and can use a simple regex like following and extract your needed contents from group1.
^.*->(.*)->.*$
Explanation:
^
- Matches start of text.*->
- Matches any text greedily and second last delimiter(.*)
-> Captures the text between second last and last delimiter in group1->
-> Matches last delimiter.*$
-> Matches some text optionally after last delimiter and end of line