如何在BigQuery中用REGEXP_EXTRACT提取id、sub_id等指定参数

Hey there! Let's get those id and sub_id values extracted properly for you. You've already nailed the offer-code extraction, but we need to tweak the regex (and fix a tiny syntax error) to target the second parameter specifically, then split it into the two values you need.

First, let's address the issue with your current regex: you used /d* instead of \d* (that's a backslash, not a forward slash) to match digits. Even with that fix, though, your regex would match any number_number pattern anywhere in the string—not just the second parameter. So let's fix that with two solid approaches:

Approach 1: Targeted Regex Extraction #

This uses regex to directly grab each value by positioning it correctly in the string:

WITH t0 as ( 
  SELECT 'ASD-1 UU; 3_4; GFAV; wyeuuw...' AS full_code 
  UNION ALL 
  SELECT '#YA2-1 SA; 23_4; AFF; lKjsdj ;uuw...' 
  UNION ALL 
  SELECT '&G2-1 O; 3_45; GFAV; wy; iiiiiiiu; euuw...'
) 
SELECT 
  -- Extract offer-code: everything from start to first semicolon, trimmed of extra spaces
  TRIM(REGEXP_EXTRACT(full_code, '^[^;]+')) AS offer_code,
  -- Extract id: digits after first semicolon (ignoring spaces) and before the underscore
  TRIM(REGEXP_EXTRACT(full_code, '^[^;]+;\\s*(\\d+)_')) AS id,
  -- Extract sub_id: digits after the underscore and before the next semicolon
  TRIM(REGEXP_EXTRACT(full_code, '_([\\d]+);')) AS sub_id
FROM t0

Breakdown:

• ^[^;]+: Matches all characters from the start of the string up to the first semicolon (your original offer-code logic, wrapped in TRIM() to clean up any trailing spaces).
• ^[^;]+;\\s*(\\d+)_: Skips the first parameter and semicolon, ignores any spaces, then captures the digits before the underscore as id.
• _([\\d]+);: Targets the underscore separator, then captures all digits until the next semicolon as sub_id.

Approach 2: Split the String into an Array (More Readable) #

If regex feels too brittle, splitting the string into a parameter array makes the logic much clearer and easier to maintain:

WITH t0 as ( 
  SELECT 'ASD-1 UU; 3_4; GFAV; wyeuuw...' AS full_code 
  UNION ALL 
  SELECT '#YA2-1 SA; 23_4; AFF; lKjsdj ;uuw...' 
  UNION ALL 
  SELECT '&G2-1 O; 3_45; GFAV; wy; iiiiiiiu; euuw...'
),
param_arrays AS (
  SELECT
    full_code,
    -- Normalize semicolons (remove spaces around them) then split into an array
    SPLIT(REGEXP_REPLACE(full_code, '\\s*;\\s*', ';'), ';') AS params
  FROM t0
)
SELECT
  params[OFFSET(0)] AS offer_code,
  -- Split the second parameter by underscore to get id and sub_id
  SPLIT(params[OFFSET(1)], '_')[OFFSET(0)] AS id,
  SPLIT(params[OFFSET(1)], '_')[OFFSET(1)] AS sub_id
FROM param_arrays

Breakdown:

• REGEXP_REPLACE(full_code, '\\s*;\\s*', ';'): Cleans up any spaces before/after semicolons so we get clean parameter values.
• SPLIT(..., ';'): Turns the string into an array where each element is a parameter.
• params[OFFSET(0)]: Grabs the first parameter (offer-code).
• SPLIT(params[OFFSET(1)], '_'): Splits the second parameter into an array, then we grab the first element as id and the second as sub_id.

I recommend Approach 2 because it's more readable—if your parameter format ever changes slightly, adjusting array offsets is simpler than tweaking regex patterns.

内容的提问来源于stack exchange，提问作者Timogavk