Search code examples
regexre2

How to Fix regular expression capturing group error Bigquery?

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 ?

Solution

  • Try this instead:

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