Replace a Regex look ahead for Google Big Query

Question

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?

score 1 · Accepted Answer · answered Aug 14 '22 at 19:59

1

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

answered Aug 14 '22 at 19:59

Mikhail Berlyant

165,386
8
154
230

array reverse is a good solution! Thank you! – HAIDER TAYYAB Aug 14 '22 at 21:48

Pushpesh Kumar Rajwanshi · Answer 2 · 2022-08-15T07:47:57.050

0

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

Check this demo

Update: If there is no delimiter before C then just need to make before part optional using this regex,

^(?:.*->)?(.*)->.*$

Check demo where no delimiter before C

edited Aug 15 '22 at 07:47

answered Aug 14 '22 at 19:42

Pushpesh Kumar Rajwanshi

18,127
2
19
36

This however doesnt work if its just "C -> D" and theres no delimiter behind C. Any way to add that? – HAIDER TAYYAB Aug 14 '22 at 21:42
@MustafaTahir: Yes, if there is no delimiter behind C, a small modification in regex will do the needful. All that is needed is make that part optional. Updated the answer. – Pushpesh Kumar Rajwanshi Aug 15 '22 at 07:45

Replace a Regex look ahead for Google Big Query

2 Answers2