如何在BigQuery中用REGEXP_EXTRACT提取多关键词左侧文本

你的正则表达式问题出在**|的分组逻辑和贪婪匹配**上。原表达式^(.*)department of|school of会被拆分为两个独立分支：^(.*)department of和school of，导致匹配school of时捕获组1为空，且贪婪的.*可能会过度匹配。

正确的实现方式 #

使用非贪婪匹配+分组包裹关键词，再配合coalesce（或ifnull）处理无匹配的情况，确保未找到关键词时返回原字段：

SELECT 
  institute_name,
  COALESCE(
    REGEXP_EXTRACT(institute_name, r'^(.*?)\s*(department of|school of)', 1),
    institute_name
  ) AS extracted_institute
FROM your_table;

正则逻辑说明 #

• ^：匹配字符串开头
• .*?：非贪婪匹配任意字符，确保只捕获到第一个department of/school of之前的内容
• \s*：匹配关键词前的任意空白字符（兼容空格分隔的情况）
• (department of|school of)：将两个关键词作为一个整体匹配，避免|拆分逻辑错误
• COALESCE(..., institute_name)：当正则未匹配到关键词时，返回原字段值

测试验证 #

用示例数据测试：

SELECT 
  institute_name,
  COALESCE(
    REGEXP_EXTRACT(institute_name, r'^(.*?)\s*(department of|school of)', 1),
    institute_name
  ) AS extracted_institute
FROM (
  VALUES 
    ('pomona college department of chemistry'),
    ('rutgers college school of engineering chemical engineering'),
    ('stanford university'),
    ('nyu department of math school of arts')
) AS test_data;

输出结果：

原表达式问题分析 #

1. |的优先级错误：原表达式中|将正则拆分为^(.*)department of和school of两个独立分支，匹配school of时，捕获组1没有对应内容，返回空值
2. 贪婪匹配风险：.*会尽可能匹配更多字符，若字符串中存在多个关键词，可能会捕获到最后一个关键词之前的内容，而非第一个

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