如何在BigQuery中用正则表达式提取关键词Years前的数值区间

可以用REGEXP_EXTRACT_ALL结合UNNEST来一次性提取所有符合要求的数值区间，解决你只能提取最后一个区间、适配性差的问题，代码如下：

WITH example AS (
  SELECT "1-10 Years, 2 to 4 Years | 6-9 Years. 3-4 Years" AS name
)
SELECT 
  TRIM(interval_str) AS result
FROM example,
UNNEST(REGEXP_EXTRACT_ALL(name, r'(\d+(?:\s+(?:to|-)\s+\d+))\s+Years')) AS interval_str

代码说明 #

• REGEXP_EXTRACT_ALL：BigQuery中专门用于提取所有匹配正则表达式的结果，返回一个数组，正好满足提取多个区间的需求
• 正则表达式r'(\d+(?:\s+(?:to|-)\s+\d+))\s+Years'：
  • \d+：匹配区间开头的数字
  • (?:\s+(?:to|-)\s+\d+)：匹配用to或-连接的后半段数字（?:表示非捕获组，只用来匹配结构，不单独提取）
  • \s+Years：匹配区间和关键词Years之间的空格
  • 外层括号()捕获我们需要的完整区间内容
• UNNEST：将REGEXP_EXTRACT_ALL返回的数组展开为多行结果，每个区间单独一行
• TRIM：清理区间前后可能存在的多余空格

原代码问题分析 #

你之前的代码存在两个核心问题：

1. REGEXP_EXTRACT只会返回匹配结果，但你的正则r'^(.*)years.*'用了贪婪匹配，实际会匹配从字符串开头到最后一个years的所有内容，最终只能定位到最后一个区间的位置
2. 用固定长度的SUBSTR截取内容，完全依赖区间字符长度固定，一旦区间长度变化（比如1-10是4个字符，2 to 4是5个字符），结果就会出错

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