How to Fix regular expression capturing group error Bigquery?-CodePudding

I am trying to move code from SQL server to Bigquery but I am having trouble with certain columns in my SELECT statement:

SELECT
       CAST(regexp_extract(local_chores, '([^&] )&([^&] )&.*', 1) as string) as origin_place_code,
      CAST(regexp_extract(local_chores, '([^&] )&([^&] )&.*', 2) as string) as origin_equipment_code,

I get an error : Regular expressions passed into extraction functions must not have more than 1 capturing group

I tried to play around with my code but I didn't manage to fix the error. Does anyone have insight into this issue ?

CodePudding user response：

Try this instead:

SELECT REGEXP_EXTRACT(local_chores, r'([^&] )&[^&] &.*') AS origin_place_code,
       REGEXP_EXTRACT(local_chores, r'[^&] &([^&] )&.*') AS origin_equipment_code,
  FROM ...