REGEXP_EXTRACT String Start with AG or TS, and capture everything after

user_16201583 0
Viewed 146

Below is the dataset examples, each row has the following values:

row value
1 AG3608-sueyfbnd-sjwfk
2 TS2649-sjwjmdaqo-wkdmfl
3 wudjeolssopp
4 sjhwu78iwjm

so on....

I want to extract values that start with either AG or TS, and capture everything after. Below is the desired outcome

row value
1 AG3608-sueyfbnd-sjwfk
2 TS2649-sjwjmdaqo-wkdmfl

I wrote something like this, but it's only capturing the the first 2 letters AG or TS regexp_extract(${column},'^(AG|TS).*') it doesn't capture everything after

looker google-bigquery regex
2 Answers

Use both a capturing and non-capturing groups:

regexp_extract(${column},'^((?:AG|TS).*)')

EXPLANATION

--------------------------------------------------------------------------------
  ^                        the beginning of the string
--------------------------------------------------------------------------------
  (                        group and capture to \1:
--------------------------------------------------------------------------------
    (?:                      group, but do not capture:
--------------------------------------------------------------------------------
      AG                       'AG'
--------------------------------------------------------------------------------
     |                        OR
--------------------------------------------------------------------------------
      TS                       'TS'
--------------------------------------------------------------------------------
    )                        end of grouping
--------------------------------------------------------------------------------
    .*                       any character except \n (0 or more times
                             (matching the most amount possible))
--------------------------------------------------------------------------------
  )                        end of \1
user_113298900

Consider below

select *, 
  regexp_extract(value, r'^(?:AG|TS)(.*)') as everything_after
from data
where regexp_contains(value,'^(AG|TS)')

if applied to sample data in your question - output is

enter image description here

user_52219440
Related