如何使用REGEX_EXTRACT提取特定字符间文本及相关优化问题

Problem 1: Avoid Duplicate Values in A2_v1 When Matching A1 #

Your current approach works, but to clean up duplicates, a CASE WHEN is indeed a straightforward and readable solution. To avoid repeating the SPLIT operation (which is more efficient), you can extract the A2 array once in a CTE or subquery, then reference it for each column.

Here's the optimized query:

WITH extracted AS (
  SELECT
    Sample_Data,
    REGEXP_EXTRACT(Sample_Data, r'(?:^|;)A1=(\d+)') AS A1,
    -- Extract the full A2 value (all comma-separated numbers)
    REGEXP_EXTRACT(Sample_Data, r'(?:^|;)A2=([\d,]+)(?:;|$)') AS A2_full,
    -- Split A2_full into an array (handle NULL if A2 doesn't exist)
    SPLIT(REGEXP_EXTRACT(Sample_Data, r'(?:^|;)A2=([\d,]+)(?:;|$)'), ',') AS A2_array
  FROM db.Sample
)
SELECT
  A1,
  -- Get first element of A2 array (or NULL if no A2)
  A2_array[SAFE_OFFSET(0)] AS A2,
  -- Return NULL if second element matches A1, else return it
  CASE WHEN A2_array[SAFE_OFFSET(1)] = A1 THEN NULL ELSE A2_array[SAFE_OFFSET(1)] END AS A2_v1
FROM extracted;

Key Improvements: #

• Uses SAFE_OFFSET instead of OFFSET to avoid errors when A2 has fewer elements (returns NULL instead of failing)
• Extracts the A2 array once in the CTE, so we don't repeat the SPLIT operation multiple times
• Updated regex to use \d+ (matches one or more digits) instead of \d* (which allows empty matches) for better accuracy
• Adjusted the A2 regex to capture the full comma-separated sequence using ([\d,]+) and end-of-string check (?:;|$) to handle cases where A2 is the last element in the string

Problem 2: Extract Multiple Segments from A2 with Multiple Commas #

To extract all comma-separated values into separate columns, you can use the same array approach with SAFE_OFFSET for each position you need. This handles cases where there are fewer values (returning NULL for missing columns) and avoids errors.

Here's the query for your extended sample data:

WITH extracted AS (
  SELECT
    Sample_Data,
    REGEXP_EXTRACT(Sample_Data, r'(?:^|;)A1=(\d+)') AS A1,
    -- Split the full A2 value into an array
    SPLIT(REGEXP_EXTRACT(Sample_Data, r'(?:^|;)A2=([\d,]+)(?:;|$)'), ',') AS A2_array
  FROM db.Sample
)
SELECT
  A1,
  A2_array[SAFE_OFFSET(0)] AS A2,
  A2_array[SAFE_OFFSET(1)] AS A2_v1,
  A2_array[SAFE_OFFSET(2)] AS A2_v2,
  A2_array[SAFE_OFFSET(3)] AS A2_v3
FROM extracted;

Output for Your Extended Sample: #

Notes: #

• If you need to handle more than 4 segments, just add more columns using A2_array[SAFE_OFFSET(n)] where n is the index (starting from 0)
• SAFE_OFFSET ensures that if there's no element at that index, it returns NULL instead of throwing an error
• The regex (?:^|;)A2=([\d,]+)(?:;|$) correctly captures the entire A2 value, even if it's the last element in the string (using $ to match end of line)

Bonus: Alternative for Dynamic Number of Segments #

If the number of comma-separated values in A2 varies widely and you don't want to define fixed columns, you can use UNNEST to turn the array into rows instead:

SELECT
  A1,
  -- Assign a position number to each segment
  CONCAT('A2_v', OFFSET) AS segment_name,
  value AS segment_value
FROM (
  SELECT
    REGEXP_EXTRACT(Sample_Data, r'(?:^|;)A1=(\d+)') AS A1,
    SPLIT(REGEXP_EXTRACT(Sample_Data, r'(?:^|;)A2=([\d,]+)(?:;|$)'), ',') AS A2_array
  FROM db.Sample
), UNNEST(A2_array) WITH OFFSET;

This will return each segment as a separate row, which can be more flexible if the number of segments isn't fixed.

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